dynamic cursor in T-sql
-
Hi ,
I am trying to execute a stored proc and need help with dynamic cursor.
CREATE PROCEDURE SP_CK_EVENTDATE
@TABLENAME NVARCHAR(100)
as declare
@sql nvarchar(2000),
@ID INT,
@DATE DATETIME
set @sql=('declare my_cur cursor for select , ID , date from dbo. ' +@TABLENAME)
BEGIN
EXEC SP_CK_EVENTDATE @sql
OPEN MY_CUR
fetch my_cur into @ID, @DATE
while @@fetch_status=0
begin
print (@id)
END
end
close my_cur
deallocate my_cur
GO
thanx
-
A few things...
You have an extra comma in the line:
set @sql=('declare my_cur cursor for select , ID , date from dbo. ' +@TABLENAME)
Try:
set @sql=('declare my_cur cursor for select ID , date from dbo. ' +@TABLENAME)
You're calling the procedure recursively, which is scary. Surely instead of:
EXEC SP_CK_EVENTDATE @sql
you just mean:
EXEC (@SQL)
And finally, you're not fetching the next record each time.
Instead of:
fetch my_cur into @ID, @DATE
while @@fetch_status=0
begin
print (@id)
END
Put:
fetch my_cur into @ID, @DATE
while @@fetch_status=0
begin
print (@id)
fetch my_cur into @ID, @DATE
END
Hope this helps...
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
(Removed incorrect information)
Why are you using a cursor for this? You do not need a cursor here. And do you really need to be able to execute the query for a dynamically chosen table? See this article for some info:
The Curse and Blessings of Dynamic SQL by SQL Server MVP Erland Sommarskog
-
I think you're mistaken here, Chris. If you create a cursor in an exec() statement, you can access it afterwards. Very handy thing, although I'm not sure it's actually by design.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Oh, I see. That's what I get for trying to tell someone how to use cursors when I never use them myself. Should have just posted the standard-anti-cursor response.
I have removed the incorrect parts from the message.
-
thanx Rob for your help ..... but it is still not working when i execute it i get the message
execute SP_CK_EVENTDATE ('emp');
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'emp'.
Help from you or others would be highly appreciated
CREATE PROCEDURE SP_CK_EVENTDATE
@TABLENAME NVARCHAR(100)
as
declare
@sql nvarchar(2000),
@ID INT,
@DATE DATETIME
set @sql=('declare my_cur cursor for select ID, date from dbo. ' +@TABLENAME)
BEGIN
EXEC (@sql)
print(@sql)
OPEN MY_CUR
fetch my_cur into @ID, @DATE
while @@fetch_status=0
begin
print (@id)
fetch my_cur into @ID, @DATE
END
close my_cur
deallocate my_cur
end
GO
-
Remove the space in
set @sql=('declare my_cur cursor for select ID, date from dbo. ' +@TABLENAME)
set @sql=('declare my_cur cursor for select ID, date from dbo.' +@TABLENAME)
is better.
If you print(@sql) BEFORE you EXEC it, you can probably see this kind of error quicker. Sorry I didn't notice it earlier.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply