CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
cdub
cdub

Reputation: 25711

Passing data into nested cursors in TSQL

I have a cursor that gets data and fetches it into @data_table. Then in the while loop I want to pass that @data_table into another cursor (as the table name) to run some more processing. I keep getting a declare @data_table error. How do I fix this?

DECLARE @var_name varchar(50)
DECLARE @data_table varchar(50)
DECLARE @data_value varchar(50)

DECLARE curDiscreteVars CURSOR LOCAL

FOR SELECT DISTINCT v.var_name, v.data_table 
    FROM dbo.vars v
    GROUP BY v.var_name, v.data_table

-- Loop through cursor, translating variable values as needed, and generate counts for each val_code for a variable
OPEN curDiscreteVars
FETCH NEXT FROM curDiscreteVars 
    INTO @var_name, @data_table

WHILE @@FETCH_STATUS  = 0
BEGIN
    --loop through all possible data values

    DECLARE curValues CURSOR LOCAL

    FOR SELECT DISTINCT @var_name  
        FROM @data_table

    OPEN curValues
    FETCH NEXT FROM curValues 
        INTO @data_value

    WHILE @@FETCH_STATUS  = 0
    BEGIN
        print @var_name



        FETCH NEXT FROM curValues 
        INTO @data_value
    END

    CLOSE curValues
    DEALLOCATE curValues

    FETCH NEXT FROM curDiscreteVars 
    INTO @var_name, @data_table
END

CLOSE curDiscreteVars
DEALLOCATE curDiscreteVars

Upvotes: 0

Views: 1053

Answers (2)

Mark Kremers
Mark Kremers

Reputation: 1789

For my part, i don't like cursors! For me cursors are evil. The give you locks and such that you don't want. What i always do is create a temp table with the values (like you normally insert into the cursor) and loop through it with a while loop

like this :

declare @currow int
      , @totrow int

create table #tmp_values (id int identity(1, 1), val int)

insert 
into   #tmp_values
select val
from   tableX

set @totrow = @@rowcount
set @currow = 1

while @totrow > 0 and @currow <= @totrow
begin
  select @val = val
  from   #tmp_values
  where id = @currow

  set @currow = @currow + 1
end

That way you have more control of things and you can re-use the tmp table

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

I'm not sure I understand what you're talking about doing, but variables cannot be used as table names. Or, really anything that's not a field name. You'll need to use dynamic SQL. That is, assign your SQL string to a variable, an then run EXEC() command.

For example:

 DECLARE @sqlcmd varchar(max)
 DECLARE @table_name sysname

 DECLARE cur_tables FOR
 SELECT name FROM sys.tables

 OPEN cur_tables
 FETCH NEXT FROM cur_tables INTO @table_name

 WHILE @@FETCH_STATUS = 0 BEGIN
 SET @sqlcmd = 'SELECT TOP 10 * FROM ' + QUOTENAME(@table_name)
 EXEC ( @sqlcmd )
 FETCH NEXT from cur_tables INTO @table_name
 END

 CLOSE cur_tables
 DEALLOCATE cur_tables

Alternately, if what you mean is that you need a location to store the data that is like a table, then create a temporary tabled for it.

Upvotes: 0

Related Questions