Is there a way to use parameters in a SQL Server cursor?

Question

I have a parent-child relationship in the database. What I need to do is loop through the parent's query, and using the parent's primary key, got get its children. The issue I am having is that I need to use a parameterized cursor (pass in the key) to do this.

Is there such a thing in SQL Server or a trick to mimic this? I tried doing this, but it didn't work:

DECLARE @value   VARCHAR(20);
DECLARE @someKey NUMERIC(19,0);

DECLARE main_curs 
CURSOR FOR SELECT value FROM someTable where key = @someKey;

SET @someKey = 12345;

OPEN main_curs
FETCH NEXT FROM main_curs INTO @value;
CLOSE main_curs
DEALLOCATE main_curs

But it seems that it doesn't pick up me setting the @someKey.

Any help on this would be greatly appreciated. Thanks!

UPDATE

I should include more information as I made the example seem too simple. I have multiple @someKey values that I need to use. As mentioned before, I have a parent-child relationship and I can have up to 6 children. So I am getting a list of parents and it's respective columns and iterating through it. While in the WHILE-LOOP, I wanted to get the primary key from the parent and call another cursor to get the child information (different columns returned). So I would do multiple calls to the child cursor with different @someKey values set. Hope that makes sense.

Solution 1

One thing you could try is using nested cursors. An example of this is on the bottom of the page titled: Using nested cursors to produce report output.

Solution 2

What you need is 2 cursors - one for the parent and one for the child .Make sure the child cursor is DECLARED inside the LOOP not outside.it will not work if you declare outside.

eg :

DECLARE @value   VARCHAR(20);
DECLARE @someKey NUMERIC(19,0);

DECLARE main_curs 
CURSOR FOR SELECT value FROM someTable where key = @someKey;

SET @someKey = 12345;

OPEN main_curs
FETCH NEXT FROM main_curs INTO @value;
while @@FETCH_STATUS = 0
BEGIN

DECLARE CHILD_CURS CURSOR FOR SELECT VALUE2 FROM CHILDTABLE WHERE value=@value;
open child_curs 
fetch next from child_curs into @x,@y

close child_curs
deallocate child_curs

FETCH NEXT FROM main_curs INTO @value;
END

CLOSE main_curs
DEALLOCATE main_curs

Solution 3

Here is how you can declare a cursor with dynamic SQL, using the 'EXEC()' function. Surprisingly this does work. For example:

DECLARE @QuotedDatabase NVARCHAR(128) = QUOTENAME('ReportServer')
DECLARE @ObjectID INT = 389576426
DECLARE @ColumnName NVARCHAR(128)
DECLARE @ColumnType NVARCHAR(128)
DECLARE @DeclareColumnCursor NVARCHAR(4000)
SET @DeclareColumnCursor = '
  DECLARE ColumnCursor CURSOR READ_ONLY FORWARD_ONLY FOR
  SELECT c.Name, t.Name
  FROM ' + @QuotedDatabase + '.sys.columns c
  INNER JOIN ' + @QuotedDatabase + '.sys.types t
  ON c.user_type_id = t.user_type_id
  WHERE c.object_id = ' + CAST(@ObjectID AS NVARCHAR) + '
  ORDER BY column_id'
EXEC(@DeclareColumnCursor)
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @ColumnName, @ColumnType
PRINT @ColumnName + ',' + @ColumnType
CLOSE ColumnCursor
DEALLOCATE ColumnCursor

Solution 4

In another place, someone suggested using a stored procedure (compiled SQL rather than an ad-hoc script) but that doesn't work either. Here's another MWE that shows the issue fairly clearly:

/* Should print:
dbNamein=master dbNameout=master
dbNamein=model dbNameout=model
dbNamein=msdb dbNameout=msdb
*/
create procedure [TestParamsWithOpenCursorStmt]
as
begin

   declare @dbNameIn [nvarchar](255) = N'tempdb',
      @dbNameOut [nvarchar](255),
      @fs [int];
   declare dbNames cursor for
      select db.[name] from [master].[sys].[databases] db
      where db.[name] = @dbNameIn;
   while (@dbNameIn != N'msdb') begin
      if @dbNameIn = N'tempdb'
         set @dbNameIn = N'master'
      else if @dbNameIn = N'master'
         set @dbNameIn = N'model'
      else if @dbNameIn = N'model'
         set @dbNameIn = N'msdb';
      open dbNames;
      fetch next from dbNames into @dbNameOut;
      set @fs = @@fetch_status;
      if @fs != 0 continue;
      raiserror (N'dbNamein=%s dbNameout=%s', 0, 0, @dbNameIn, @dbNameOut) with nowait;
      close dbNames;
   end;
   deallocate dbNames;

end;
go

execute [TestParamsWithOpenCursorStmt];

It appears that the variable (and its value at the time) gets bound to the "declare ... cursor" rather than the open cursor.

View more solutions

Comments

[Karl Hoaglund]

I tried this and found that the contents of the cursor does not change if you close the cursor, change the value in the variable, and then reopen the cursor. It appears that the cursor definition is "compiled" in the DECLARE statement. I came here to SO looking for a work-around short of redeclaring the cursor.

[Rob at TVSeries.com]

This works for a single value of @someKey, but as Karl Hoaglund says in a comment on another answer regarding this solution, "I tried this and found that the contents of the cursor does not change if you close the cursor, change the value in the variable, and then reopen the cursor. It appears that the cursor definition is "compiled" in the DECLARE statement. I came here to SO looking for a work-around short of redeclaring the cursor." which makes three of us looking for that answer, including Ascalonian per the update in the OP.

[Mitselplik]

@Rob - Per the information in the link to the Microsoft Help documentation on nested cursors posted by John Kane, your response is somewhat inaccurate. While it is true that closing the child cursor does not change the content, deallocating the child cursor forces the SQL to re-create the cursor (and thus change the content) on the next iteration of the loop. So Karl is misleading as well as this post is misleading. You can get the inner child cursor to perform as expected by deallocating it within the loop, not just closing it. So this example is accurate.