Google
 

Thursday, March 8, 2007

Dynamic declaration of cursors in MS SQL Server

Sometimes it's required to declare and use cursors to apply some logic on the results of queries that return the same columns, but using different criteria.
For example, you format a query based on user's selection in a search screen. Then you need to apply some logic that requires cursors on the result. This logic can be paging logic (as it was the case the first time I use this technique) or whatever logic defined by your application.

This dynamically generated code can look like:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10


Now, the question is, how to declare a cursor for such a dynamic query and use this cursor in static code within a stored procedure for example to apply the required logic.

This technique cames to work great:

Create Proc CursorLogic(@SQL varchar (1000))
as
declare @id int
declare @name varchar(1000)
declare @joindate datetime

Set @sql='declare cur cursor for '+ @sql
exec (@SQL)
open cur

fetch next from cur into @id,@name,@joindate
while @@FETCH_STATUS=0
Begin

print @name
-- Cursor logic goes here

fetch next from cur into @id,@name,@joindate
End
close cur
deallocate cur
Go


This creates a stored procedure that takes the @sql variable which is a SQL query.
It inserts the declare cursor statement then uses this cursor inside the stored procedure logic, just as if it was declared regularly within the stored procedure code.

This stored procedure can be called as:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10
Exec CursorLogic @SQL

Set @sql='Select ID,Name,JoinDate Fom Employee Where Name like 'M%'
Exec CursorLogic @SQL

8 comments:

Anonymous said...

I tried this, but its not working.

Rajesh
[raj.cho@gmail.com]

Hesham A. Amin said...

Rajesh,
Can you post the code that did not work for you and any errors you've got?

Anonymous said...

Hi, I have different scenario but facing problem in scripting dynamic query.
My code is something like as printed below.

###################################
DECLARE @FETCHCURIMPORT nvarchar(4000)
SET @FETCHCURIMPORT = '@WashLotId,'

SET @FETCHCURIMPORT = @FETCHCURIMPORT + @SQLELEMENTVARIABLES
SET @FETCHCURIMPORT = @FETCHCURIMPORT + '@MCL_DATE_COMPLETED,@SIS_ENTRY_DATE,@DATE_TEXT,@YEAR,@MONTH,
@SAMPLEID,@FLAGUPDATE,@COMMENTS, @RowErr, @RowId'

declare CURIMPORT CURSOR FAST_FORWARD for select * from Import1
OPEN CURIMPORT
FETCH NEXT FROM CURIMPORT
INTO @FETCHCURIMPORT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'we are printing in cur import'
FETCH NEXT FROM CURIMPORT
INTO @FETCHCURIMPORT
END
###################################

@FETCHCURIMPORT contains 21 variables in which cursor values will be fetched into. But SQL is considering "@FETCHCURIMPORT" as single variable instead of 21 variables and giving error that number of columns in select stmt and variables are not matching.

Has anyone come across such scenario or can you help me scripting a executible SQL query out of this?

Regards,
PRASHANT

Hesham A. Amin said...

PRASHANT,
This is not how variables work in T-SQL.
When you add 2 variables as :
SET @FETCHCURIMPORT = @FETCHCURIMPORT + @SQLELEMENTVARIABLES
You just add the (value) of @SQLELEMENTVARIABLES to the (value) of @FETCHCURIMPORT and putting the result in @FETCHCURIMPORT again.
@FETCHCURIMPORT now contains the new value. It's not composed of all the added variables.
The fetch statement should be like:
FETCH NEXT FROM CURIMPORT
INTO @SQLELEMENTVARIABLES ,@MCL_DATE_COMPLETED ,@SIS_ENTRY_DATE.....etc.
As you already hard-code the columns in the query, why do you want to merge them in 1 variable anyway?
If you necessarily need to do it, you may need to concatenate the whole SQL statement then execute it, which is a bad thing in general.

Anonymous said...

thanks for simple example to use dynamic sql as loop cursor. it works. I am from Oracle background and got a small assignment in SQL Server. due to lack of knowledge I was struggling. you saved my day.

imzi said...

Thanks brother. This was helpful

Anonymous said...

Thanks for the help Amin. Saved me a good amount of time.

BTW... you are missing some single quotes in your examples.

Anonymous said...

My company has been working with the same professional server which is the elegant choice on higher level. All these variables are the same that I have been assuming in this programming.