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.

Answer 1

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

Answer 2

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