Declare a cursor with a string

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

SQL Server Development (2000)

Declare a cursor with a string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-24 : 08:03:10

Brian writes "I'm using sql 2000. I'd like to declare a cursor using a string. Here's a simplified example of what I want (but this doesn't work):

-------------------------------------------------------
Declare
@SelectStatement varchar(8000),
@WhereStatement varchar(8000)

Select @SelectStatement = 'Select * from my_table "

if @Dates = 'B'
begin
Select @WhereStatement = "Where birthdate = @Date"
end

if @Dates = 'H'
begin
Select @WhereStatement = "Where hiredate = @Date"
end

Declare CSR Cursor for
@SelectStatement + @WhereStatement

Open CSR

[Lots of processing here]

Close CSR
Deallocate CSR
-------------------------------------------------------

This is simplified because in reality there could be multiple date ranges, etc. In the past what we've done is:

-------------------------------------------------------
if @Dates = 'B'
begin
Declare CSR Cursor for
Select * from my_table where birthdate = @Date
end

if @Dates = 'H'
begin
Declare CSR Cursor for
Select * from my_table where hiredate = @Date
end

Open CSR
-------------------------------------------------------

This can be a waste if the select statement is HUGE and you've got multiple where clauses. I could end up repeating an identical select statement 10 times with 10 different where clauses.

Any ideas? Your help is GREATLY appreciated."

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-24 : 09:06:07

Firstly, there is so much you can do with SET based methods a lot faster and easier than by using cursors. Secondly, ditto for dynamic sql (constructing SQL in strings). Not it only is dynamic SQL slower, its also prone to security problems. If you post your table structures and requirements, we could help you do this right.

Owais

Make it idiot proof and someone will make a better idiot

airjrdn
Starting Member

35 Posts

Posted - 2003-09-24 : 17:42:26

Another option for you might be something along the lines of:

Create Procedure dbo.spProcName
    @Birthdate Datetime = Null,
    @HireDate Datetime = Null
As
Declare CSR Cursor for 
Select *
from   my_table
Where  (Birthdate = @Birthdate or @Birthdate is Null)
and    (Hiredate = @Hiredate or @Hiredate is Null)

Open CSR

[Lots of processing here]

Close CSR
Deallocate CSR

This will allow you to pass in Birthdate, Hiredate, or both, and the select statement will grab the correct records accordingly.

Note that this will allow you to not pass either in, so if you want to ensure that at least one of them was passed in, you'll need to incorporate that check as well.

airjrdn
Starting Member

35 Posts

Posted - 2003-09-30 : 10:18:56

Just curious, have you tried the solution I offered?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources