Declaring cursor with parameter ?? How ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Declaring cursor with parameter ?? How ??

Author

Topic

Karander
Starting Member

34 Posts

Posted - 2004-08-31 : 09:41:12

I declared cursor in stored proc. Sth like this:
declare kur_sel cursor local for
select * from table1 @param

in @param i have; "where field1 > 100"
or there can be: @param = "where field555 < 60"

how to assign there @param ??

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 09:42:53

u must use dynamic sql.
look at this:

http://www.sqlteam.com/item.asp?ItemID=2077

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 09:50:23

may i please ask why

why a cursor at all? Is it necessary? What are you trying to do, we might be able to give you a better suggestion than a cursor built from dynamic SQL. That just sounds painful

Corey

Karander
Starting Member

34 Posts

Posted - 2004-08-31 : 09:50:53

HeY!
Thnak you for answer, but I think that with declaring statement as parameter in declaring cursor there is not wriitten about it...
i cano not use exec to declare cursor with statement, so, how ?

Karander
Starting Member

34 Posts

Posted - 2004-08-31 : 09:54:51

quote:
Originally posted by Seventhnight

may i please ask why

why a cursor at all? Is it necessary? What are you trying to do, we might be able to give you a better suggestion than a cursor built from dynamic SQL. That just sounds painful
Corey

Heh, in parameter of oprocedure i jave to pass what field it is search by ... and i have different field's names to pass ...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 10:08:54

So you are using a cursor to determine which field to search?

Why couldn't you use dynamic sql to build a select statement?

Declare @str nvarchar(1000),
@column nvarchar(100),
@param nvarchar(100),

Set @column = 'name'
Set @param = 'S%'
Set @str = 'Select * from sysobjects Where ' + @column + ' like ''' + @param + '''

Exec(@str)

Corey

Karander
Starting Member

34 Posts

Posted - 2004-08-31 : 10:37:02

Heh, I can do it, but i have to use cursor!!!! to get record by record. So. How to built sql statement to declare cursor ???

quote:
Originally posted by Seventhnight

So you are using a cursor to determine which field to search?

Why couldn't you use dynamic sql to build a select statement?

Declare @str nvarchar(1000),
@column nvarchar(100),
@param nvarchar(100),

Set @column = 'name'
Set @param = 'S%'
Set @str = 'Select * from sysobjects Where ' + @column + ' like ''' + @param + '''

Exec(@str)

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 10:44:58

what do you mean record by record? Is the column you are searching on different for each record?? Can you show me some sample data that demonstrates what you are trying to do??

Sorry If it seems I'm being difficult, I'm just trying to understand what you need...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 11:17:48

what's interesting to me is that he absolutly needs a cursor :)

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 12:46:25

... yeah its like that everywhere. I had to battle my own coworkers about cursors for a while. Converts now they are.

Corey

Karander
Starting Member

34 Posts

Posted - 2004-08-31 : 16:11:13

My question is, is that possible oR NOTTTT ????
Can i add from the parameter sql statetement to cursor declare?
This is very simple question. Yes or no, if yes, how?

Thank you

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 16:23:05

Yes! It can be done.

quote:

Declare @str nvarchar(2000)

Declare @param nvarchar(100)
Set @param = 'WHERE LastName like ''B%'''

Set @str = 'DECLARE Employee_Cursor CURSOR FOR '
Set @str = @str + 'SELECT LastName, FirstName FROM Northwind.dbo.Employees ' + @param + ' '
Set @str = @str + 'OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor END CLOSE Employee_Cursor DEALLOCATE Employee_Cursor'

Exec(@str)

The point we are trying to make is that it probably shouldn't be done. By doing this you combine two clunky methods of data manipulation. Dynamic SQL will not run as fast as a normal query. Cursors are often memory intensive and are generally the wrong approach in a set based language.

To save you the trouble, we were trying to steer you away from a dynamic cursor. However, since you insist on beating your server to death... have at it.

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 16:25:51

what is ran in exec() is a closed batch, so you can if you do everything in there. but if u want to open a cursor for use outside, then no.

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-31 : 16:25:59

quote:
Originally posted by Karander

My question is, is that possible oR NOTTTT ????
Can i add from the parameter sql statetement to cursor declare?
This is very simple question. Yes or no, if yes, how?

Thank you

It's past somebody's nap time.....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-31 : 16:39:01

Here's some rope


USE Northwind
GO

DECLARE @sql varchar(8000), @param varchar(255)

SELECT @param = 'Orderid > 11000'

SELECT @sql = 'DECLARE myCursor99 CURSOR FOR SELECT OrderId FROM Orders WHERE ' + @param + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'OPEN myCursor99'+ CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'DECLARE @OrderId int' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'FETCH NEXT FROM myCursor99 INTO @OrderId' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'SET NOCOUNT ON' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'WHILE @@FETCH_STATUS = 0 BEGIN' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'SELECT @OrderId' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'FETCH NEXT FROM myCursor99 INTO @OrderId END' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'CLOSE myCursor99' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'DEALLOCATE myCursor99' + CHAR(13)+CHAR(10)
SELECT @sql = @sql + 'SET NOCOUNT OFF' + CHAR(13)+CHAR(10)

EXEC(@sql)
GO

Brett

8-)

Karander
Starting Member

34 Posts

Posted - 2004-09-01 : 10:22:48

Okay i see
is it possible to declare cursor with statment in variable and then use it in normal code not dynamic ??

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 10:24:24

OK, I guess you don't have to go to dynamic heaven...you could also...


DECLARE @sql varchar(8000), @param varchar(255)
SELECT @param = 'Orderid > 11000'
SELECT @sql = 'DECLARE myCursor99 CURSOR FOR SELECT OrderId FROM Orders WHERE ' + @param + CHAR(13)+CHAR(10)
EXEC(@sql)
OPEN myCursor99
DECLARE @OrderId int
FETCH NEXT FROM myCursor99 INTO @OrderId
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0 
  BEGIN
	SELECT @OrderId
	FETCH NEXT FROM myCursor99 INTO @OrderId 
  END
CLOSE myCursor99
DEALLOCATE myCursor99
SET NOCOUNT OFF

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 10:25:39

no.
how about if u tell us what u need to do with that cursor so we can get rid of it for you...

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 10:28:05

heh Brett, somehow i really doubt it's that simple what he wants to do....

Go with the flow & have fun! Else fight the flow :)

Karander
Starting Member

34 Posts

Posted - 2004-09-01 : 10:28:19

ufff, really Thanks, and that is is what i have wanted !!! :)) thx I hope it will be working ;) Bye bye

quote:

Originally posted by X002548

OK, I guess you don't have to go to dynamic heaven...you could also...


DECLARE @sql varchar(8000), @param varchar(255)
SELECT @param = 'Orderid > 11000'
SELECT @sql = 'DECLARE myCursor99 CURSOR FOR SELECT OrderId FROM Orders WHERE ' + @param + CHAR(13)+CHAR(10)
EXEC(@sql)
OPEN myCursor99
DECLARE @OrderId int
FETCH NEXT FROM myCursor99 INTO @OrderId
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0 
  BEGIN
	SELECT @OrderId
	FETCH NEXT FROM myCursor99 INTO @OrderId 
  END
CLOSE myCursor99
DEALLOCATE myCursor99
SET NOCOUNT OFF

Brett

8-)

Karander
Starting Member

34 Posts

Posted - 2004-09-01 : 10:29:54

By the way, what does "nocount" do ?? ;)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources