Reputation: 440
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
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