Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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
0
theresamary
Asked:
theresamary
  • 4
1 Solution
 
KingTCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now