Oracle to SQL Server - post for fellow geeks

[janewilliams20]

And so I'll put most of it under a cut. I'm moving a bunch of code from PL/SQL to T_SQL: this is about the lack of %ROWTYPE in T-SQL.

Quick reminder: you have a table with ten billion columns, you want to pull one row into some variables to play with, so in Oracle, you do this:

myVarStructure mytable%rowtype; --declare the variables
Cursor c_get_myTable IS -- set up the query
select * from myTable
where ... ;
open c_get_myTable ; -- and use it, probably inside a loop
fetch c_get_myTable into myVarStructure;
close c_get_myTable;

Job done. You have a structure holding ten billion variables, each with the same name, type, and contents as the column it's been loaded with.

"Ten billion", here, is shorthand for "far more than I want to type in by hand thank you very much".

T-SQL has no %rowtype. I'm going to have to declare every variable, and then write an insert statement a mile long, select @var1 = col1, var2 = col2.... etc.

Or, I could read a very useful article, and get Ideas.
"PL/SQL and T-SQL; Birds of a Feather Syntax Examples"

That shows you how to query the syscolumns and systypes tables to get T-SQL to do the typing for you. It only provides you with code to cut&paste and save, it doesn't regenerate it for you if you change your table definition (though I think I can see how to make it do that), but it will save an awful lot of typing. Here's my version of the declaration generator:


IF OBJECT_ID ( 'Rowtype_multi', 'P' ) IS NOT NULL
DROP PROCEDURE Rowtype_multi;
GO
create procedure Rowtype_multi
@TableName varchar(20)
AS
select
'Declare @' +
c.name +
space(30-len(c.name)) +
t.name +
case t.name
when 'binary' then '(' + convert(varchar,c.length) + ')'
when 'varbinary' then '(' + convert(varchar,c.length) + ')'
when 'char' then '(' + convert(varchar,c.length) + ')'
when 'varchar' then '(' + convert(varchar,c.length) + ')'
when 'float' then '(' + convert(varchar,c.length) + ')'
when 'decimal' then '(' + convert(varchar,c.prec) + ',' +
convert(varchar,c.scale) + ')'
when 'numeric' then '(' + convert(varchar,c.prec) + ',' +
convert(varchar,c.scale) + ')'
else ''
end
from syscolumns c
join systypes t on c.xusertype=t.xusertype
where id=object_id(@tableName)
order by colorder
GO


Call that with
Rowtype @TableName = 'employee'
and get this sort of thing:
Declare @emp_id empid
Declare @fname varchar(20)
...


Now you need the select that will populate that lot.

IF OBJECT_ID ( 'SelectIntoList', 'P' ) IS NOT NULL
DROP PROCEDURE SelectIntoList;
GO
create procedure SelectIntoList
@TableName varchar(20)
AS
select
'@'+c.name + + ' = '+c.name+', ' "List"
from syscolumns c
join systypes t on c.xusertype=t.xusertype
where id=object_id(@tableName)
order by colorder
GO

Stick your own "select" on the start and "from..." on the end, but that will give you
@var1 = col1, @var2 = col2,....

I may yet convert that to dynamic SQL that really does define itself on the fly, but for this job, we only change the table definition every five years or so.