April 9, 2002 at 3:35 pm
I am an Oracle developer who is now switching to SQL Server and am wondering if there is a similiar way to code the %TYPE of oracle in SQL Server. For example in ORacle if you were creating a procedure you can declare the data type of a parameter in the following fashion:
table_name.column_name%TYPE
This way the parameter is the exact type of the column without always remembering what the datatype is.
Can this be done in SQL Server 2000.
Thanks
April 9, 2002 at 5:19 pm
No, you have to declare the type explicitly, no function to do it like %type did for you.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 11, 2002 at 3:18 am
Hi, I'm an ORACLE developer too, but I've had to get heavily into SQL Server (quite like it). You might benefit from some gotchas and goodies I've found in my experience so far.
Just like there's no direct equivilant of the %TYPE so there's no equivilant of %ROWTYPE. Cursors are a little more tricky because of this, you have to declare a variable for each column in your cursor to fetch into. I haven't found an equivilant of the Cursor FOR loop either. Beware you can create Cursors at Global and Local scope. (local meaning the Cursor dies when you exit a Function/Procedure). There is a parameter setting when a database is created which controls whether Cursors are defaulted Global or Local (whichever way it's set you have to declare if you want it otherwise in your Cursor declartion.
Error-handling is from the stone-age. There is no EXCEPTION block and I've no equivilant to PRAGMA. The only thing you have is a transitory @@ERROR system object which is difficult to pass consistently.
I cannot find any equivilant to the PLSQL Package structure for combining procedures.
Beware your JOIN staments, SQL Server seems to prefer the verbose INNER, OUTER etc rather than the more concise ORACLE structure for all but the simplest of joins. I reccomend a Query-Builder tool such as that in BMC's SQL Programmer to save you typing and migranes.
On the up side check out DTS wow has that got SQL LOader beat big-time. One of the best data-transfer mechanisms I've worked with. XML support is ace too. Check out the Computed Column data type, it replaces all those INSERT/UPDATE triggers that compute the value of two or more columns into another. Oh and be wary of Triggers, they DO NOT have the same Statment/Row levels that ORACLE has, for example I haven't found a FOR EACH ROW equivilant, you can finde triggers only effect the last row in a batch.
There's more, post me if I can help
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply