Use of input Parameter instead of select statement in cursor declaration

Hai,
I am having a stored procedure. I want to use a input parameter instead of select statement while declaring the cursor like this?

create procedure proc1
@qry varchar(100) as

declare cursor1 cursor for @qry


Is this possible? If yes, pls help us

Thanks,
theresa
theresamaryAsked:
Who is Participating?
 
KingTConnect With a Mentor Commented:
It is possible. You should do something like this:

create procedure proc1 @qry varchar(100) as
declare @sql varchar(1024)

set @sql = 'declare cursor1 cursor for ' + @qry
execute(@sql)
0
 
theresamaryAuthor Commented:
Hai King,
 Thanks.Its working well.
theresa
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
duplicate q, please delete it:
https://secure.experts-exchange.com/mssql/Q.20287919.html

CHeers
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Here is my comment (i know i'm not first one ...)

only dynamic SQL allows for this:

declare @s varchar(100)
set @s = 'select * from ta'

exec ('
declare @r varchar(100)
declare @id int
declare @name varchar(30)
declare c cursor  for ' + @s + '
open c
fetch c into @id,@name
while (@@fetch_status<>-1)
begin
set @r = cast(@id as varchar(10)) + '' '' + @name
print @r
fetch c into @id, @name
end

close c
deallocate c ')

Now, if you can avoid the cursor, do so ...

CHeers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
theresamary, if you admit that KingT's comments is OK for you, you should accept his comment as answer.

CHeers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.