CODEWITHSUNDEEP
sql-serverdatabase-cursor
Mr.J
Mr.J

Reputation: 440

How to put a parameterized sql transaction in a cursor?

For a more detailed explanation, I have this scenario.

I know this is the correct way to initiate a cursor:

DECLARE cur CURSOR LOCAL FOR 
    SELECT NAME FROM namelist;
OPEN cur;

But I need it to be like this

DECLARE cur CURSOR LOCAL FOR 
    SELECT NAME FROM namelist where nameID in ('1,2,3,4');
OPEN cur;

The reason is Is I am passing the numbers (1,2,3,4) on a parameter of my stored procedure

so lets say I have this parameter, which was taken from the system

declare @namelist as varchar(20) 
set @namelist = '1,2,3,4'

I want the value of the parameter to be used in my IN statement

DECLARE cur CURSOR LOCAL FOR 
    SELECT NAME FROM namelist where nameID in (@namelist)
OPEN cur;

but the only way I know how to do that is by using this method:

    exec (SELECT NAME FROM namelist where nameID in ('+@namelist+'))

however this method cannot be used like this:

DECLARE cur CURSOR LOCAL FOR 
     exec (SELECT NAME FROM namelist where nameID in ('+@namelist+')
OPEN cur;

the is an error that state

Incorrect syntax  near exec, expecting'(', SELECT, or WITH

So can you guys give me an idea how to use a parameterized statement in a cursor?

Upvotes: 0

Views: 128

Answers (1)

Thom A
Thom A

Reputation: 96055

I doubt you really need a CURSOR here, almost every time (I'd go as far as 99% of the time) a CURSOR is being used incorrectly and a set based method would be far better.

Anyway, for what you want here, you'll be better off using a table-value parameter. I assume this is inside an SP, so you'll have to do something like this:

CREATE TYPE dbo.IntList AS TABLE (I int);
GO

ALTER YourSP @FirstParam varchar, @NameID dbo.IntList READONLY AS
BEGIN ...

    DECLARE cur CURSOR LOCAL FOR 
         SELECT NAME FROM namelist NL JOIN @NameID N ON NL.NameID = N.I;
    ...

END;

GO

DECLARE @NameID dbo.IntList;
INSERT INTO @NameID
VALUES(1),(2),(3),(4);

EXEC YourSP @FirstParam = 'SomeValue', @NameID = @NameID READONLY;

If you do want to move to a set based approach here, however, you'd be better off asking a different question. How to pass a list of items (to a CURSOR) and how to change a iterative method to a set based method are very different, and unrelated, questions.

Upvotes: 2

Related Questions