May 25, 2017 at 3:29 am
Hi,
I am trying to write a script that will delete data from a given table using cursors.
As shown in the script below, if the delete..where current of..cursor script is executed directly, it is successful.
declare @objcursor as cursor
declare @delquery as nvarchar(max)
declare @vsql as nvarchar(max),@vquery as nvarchar(max)set @vquery = 'select * from Test_A'
set @vsql = 'set @cursor = cursor for ' + @vquery + ' open @cursor;'
exec sys.sp_executesql
@vsql
,N'@cursor cursor output'
,@objcursor output
fetch next from @objcursor
while (@@fetch_status = 0)
begin
delete from Test_A where current of @objcursor; -- This is executing successfully
fetch next from @objcursor;
end
close @objcursor
deallocate @objcursor
But the problem is when I try to write the delete script as dynamic query. The reason is that table name Test_A is not constant and this changes based on an outer cursor(not included in this script).
.
.
.
while (@@fetch_status = 0)
begin
SET @delquery = 'delete from Test_A where current of @objcursor';
exec sys.sp_executesql @delquery
fetch next from @objcursor;
end
.
.
.
Making the delete script dynamic is giving error -- Must declare the scalar variable "@objcursor"
Could you please let me know what I am missing here?
Thanks!
May 25, 2017 at 3:40 am
From what I can tell, you're deleting one row at a time from Test_A until the table is empty. Is that right? Why use a cursor instead of deleting all rows at once?DELETE FROM Test_A
John
May 25, 2017 at 3:47 am
Thanks for your reply!
Not really. As mentioned, there is more code to this script which I might not be able to include here.
I will be passing an ID field here 'set @vquery = 'select * from Test_A where ID = <outer cursor value>'
So I need to delete only specific records fetched by select * from Test_A where ID = <outer cursor value>.
May 25, 2017 at 3:56 am
You can still do that without a cursor by using a WHERE clause in your delete statement. It's difficult if you can't share all your code.
The answer your original question is I don't know why your cursor doesn't work. Every time I've used a cursor, I haven't declared it with an "@" at the beginning of its name. That may or may not be your problem here. But I'd still question the need for a cursor in the first place, with the extra complexity and loss of performance that it brings.
john
May 25, 2017 at 4:47 am
reachprth - Thursday, May 25, 2017 3:29 AMHi,I am trying to write a script that will delete data from a given table using cursors.
As shown in the script below, if the delete..where current of..cursor script is executed directly, it is successful.declare @objcursor as cursor
declare @delquery as nvarchar(max)
declare @vsql as nvarchar(max),@vquery as nvarchar(max)set @vquery = 'select * from Test_A'
set @vsql = 'set @cursor = cursor for ' + @vquery + ' open @cursor;'
exec sys.sp_executesql
@vsql
,N'@cursor cursor output'
,@objcursor outputfetch next from @objcursor
while (@@fetch_status = 0)
begin
delete from Test_A where current of @objcursor; -- This is executing successfully
fetch next from @objcursor;
end
close @objcursor
deallocate @objcursorBut the problem is when I try to write the delete script as dynamic query. The reason is that table name Test_A is not constant and this changes based on an outer cursor(not included in this script).
.
.
.
while (@@fetch_status = 0)
begin
SET @delquery = 'delete from Test_A where current of @objcursor';
exec sys.sp_executesql @delquery
fetch next from @objcursor;
end
.
.
.
Making the delete script dynamic is giving error -- Must declare the scalar variable "@objcursor"
Could you please let me know what I am missing here?Thanks!
The SQL which you are executing dynamically is in a different, inner, session to the session calling it.
You can, however, create a #temp table in an outer session which is used in a subsequent dynamic batch. Not only will this work, it would encourage you to drop the cursor with it's miserable performance in favour of decent set-based operations.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 25, 2017 at 4:49 am
Yes John. I understand this can be done through a simple DELETE statement. The actual code we had earlier was the same, but now as we are generalizing the process and also as we foresee some additional requirements, I have to write the delete within the cursor.
My problem is only with these two lines
SET @delquery = 'delete from Test_A where current of @objcursor';
exec sys.sp_executesql @delquery
I guess there is some problem with the way the execution happens 'exec sys.sp_executesql @delquery'.
As for the usage of @ in cursor variable, I did not face any issues. Omitting @ gives me errors like cursor does not exist or other declaration errors.
Thanks!
May 25, 2017 at 5:03 am
ChrisM@Work - Thursday, May 25, 2017 4:47 AMThe SQL which you are executing dynamically is in a different, inner, session to the session calling it.
You can, however, create a #temp table in an outer session which is used in a subsequent dynamic batch. Not only will this work, it would encourage you to drop the cursor with it's miserable performance in favour of decent set-based operations.
Thanks for your reply Chris!
There are certain restrictions when it comes to creating new temp tables in production. I need to check if this can be done.
Thanks!
May 25, 2017 at 5:27 am
reachprth - Thursday, May 25, 2017 5:03 AMChrisM@Work - Thursday, May 25, 2017 4:47 AMThe SQL which you are executing dynamically is in a different, inner, session to the session calling it.
You can, however, create a #temp table in an outer session which is used in a subsequent dynamic batch. Not only will this work, it would encourage you to drop the cursor with it's miserable performance in favour of decent set-based operations.Thanks for your reply Chris!
There are certain restrictions when it comes to creating new temp tables in production. I need to check if this can be done.Thanks!
There shouldn't be any arbitrary restrictions to creating local temporary tables in any SQL Server environment - they are an essential programming tool.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 30, 2017 at 6:13 am
Thanks for your help Chris and John!
I was able to make this work using the code below.
declare @objcursor as cursor
declare @delquery as nvarchar(max)
declare @vsql as nvarchar(max),@vquery as nvarchar(max)
set @vquery = 'select * from Test_A' -- 'Test_A' will be replaced by a variable from an outer cursor
set @vsql = 'set @cursor = cursor for ' + @vquery + ' open @cursor;'
exec sys.sp_executesql
@vsql
,N'@cursor cursor output'
,@objcursor output
fetch next from @objcursor
while (@@fetch_status = 0)
begin
SET @delquery = 'delete from Test_A where current of @objcursor'; -- 'Test_A' will be replaced by a variable from an outer cursor
exec sys.sp_executesql @delquery
,N'@objcursor as cursor'
,@objcursor
<some code goes here to track the progress and commit transaction based on cursor count>
fetch next from @objcursor;
end
close @objcursor
deallocate @objcursor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy