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:
I tried this, but its not working.
Rajesh
[raj.cho@gmail.com]
Rajesh,
Can you post the code that did not work for you and any errors you've got?
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
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.
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.
Thanks brother. This was helpful
Thanks for the help Amin. Saved me a good amount of time.
BTW... you are missing some single quotes in your examples.
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.
Post a Comment