Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Use of input Parameter instead of select statement in cursor declaration

Posted on 2002-04-12
6
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 1

Accepted Solution

by:
KingT earned 800 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 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