July 31, 2003 at 9:46 am
a fetch:
declare @var1, @var2, ... @varn
fetch my_cursor into @var1, @var2, ... @varn
becomes rather cumbersome when using say 280 columns. In oracle there's something as "%rowtype"
Is it possible to construct something which at least resembles a little of this functionality in SQL server ?
(a record structure declaration or something)
BTW.: we are rewriting as many cursors into set based statements, but this is in case such a solution just doesn't cut it ...
Thanks in advance
August 1, 2003 at 3:27 am
I'm not aware of anything like that. I believe that you have to declare and fetch each variable.
Jeremy
August 1, 2003 at 4:59 am
No there (at this point) is nothing like it based on your statement.
However, if you can post the code we might see an alternate solution. Also post the table DDL if you can so we understand the datatypes.
August 1, 2003 at 5:09 am
@jeremy-2 Kemp, @Antares686 thanks for the info. I can't post code/DDL because this is problem that will be most likely in a further state of our datawarehouse project. The load into ODS will be using DML statements with joins (1 for updates, 1 for inserts and 1 for deletes). This question is about transformations from ods to ads. (not yet build)
There's something like TABLE variable in SQL server, doesn't this help me out somehow ?
--text cut out of MSDN: ---
Remarks
Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.
Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.
CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.
table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply