Solved

Use of input Parameter instead of select statement in cursor declaration

Posted on 2002-04-12
6
326 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 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