Solved

Use of input Parameter instead of select statement in cursor declaration

Posted on 2002-04-12
6
319 Views
Last Modified: 2008-03-06
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
Comment
Question by:theresamary
  • 4
6 Comments
 
LVL 1

Accepted Solution

by:
KingT earned 200 total points
ID: 6936450
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
 

Author Comment

by:theresamary
ID: 6936459
Hai King,
 Thanks.Its working well.
theresa
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6936462
duplicate q, please delete it:
https://secure.experts-exchange.com/mssql/Q.20287919.html

CHeers
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6936464
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6936466
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
 
LVL 143

Expert Comment

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

CHeers
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question