Cursor dynamic SQL revisited 3

[TroyMcClure]

I had asked recently about creating dynamic sql, ie, a string variable with a select statement, then how do I execute that.

I answered my own question by finding the sp_executesql proc.

Now I need to open a cursor using a sql string stored in a variable. I tried
"...cursor local for @sqvariable"
And
"...cursor local for sq_executesql @sqvariable"

and neither works. How would I do this?
Thanks,
T

 

[JohnDTampaBay]

Easier than you might think. Good luck!

DECLARE @s varchar(1000), @t varchar(100)

SET @s = 'DECLARE CursorName CURSOR FOR
	<Insert Cursor Select Statement>'
	
EXEC(@s)

OPEN CursorName

<Insert Cursor Manipulation Code>

CLOSE CursorName
DEALLOCATE CursorName

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[donutman]

Oh great! Well, we all know the old saw, Power corrupts and absolute power corrupts absolutely. It even works in the cyber world.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TroyMcClure]

John,
I appreciate your help. I'm getting errors with the following code:

CREATE PROCEDURE dbo.test3 AS
declare @sq varchar(1000)
declare @empname varchar(40),@c cursor
set @sq= 'set @c = cursor for Select empname from tblEmp WHERE Empid > 0  '
Exec (@sq)
open @c
fetch @c into @empname
while @@fetch_status = 0
print @empname
GO

I get the errors:
Must declare the variable '@c'.
--and--
The variable '@c' does not currently have a cursor allocated to it

Notice I had to declare @c before putting it in the string variable, since if I didn't, I couldn't even save the sp. I also used Set instead of Declare in the string. I tried it with Declare, and it failed also.

Can you spot what's wrong here? Thanks,
--T

 

[JohnDTampaBay]

You shouldn't have to DECLARE a variable for the cursor. The declaration in the dynamic sql should be enough. Do you get errors with this code?

CREATE PROCEDURE dbo.test3 AS
DECLARE @sq varchar(1000)
DECLARE @empname varchar(40)

SET @sq= 'DECLARE EmployeeCursor CURSOR FOR
          SELECT empname FROM tblEmp WHERE Empid > 0  '

EXEC(@sq)
OPEN EmployeeCursor 

FETCH NEXT FROM EmployeeCursor INTO @empname

WHILE @@fetch_status = 0
BEGIN
    PRINT @empname
    FETCH NEXT FROM EmployeeCursor INTO @empname
END

CLOSE EmployeeCursor 
DEALLOCATE EmployeeCursor

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[TroyMcClure]

That works perfectly. Thanks very much!

[aside]
I will add that I'm aware of the large contingent here of 'anti-cursor' folks, and I don't disagree that cursors are a performance hit and dynamic sql leaves the door open for sql injection.

If it were a perfect world with perfect systems, I may not need either of the two. But I work in a very imperfect world and I have a set of records that are spit out by a machine and I need to pick the right record out of several that are, for all intents and purposes, identical. The 'right' choice is based on, among other things, proximity in the table to other similar records and fuzzy tolerance ranges based on other fields of nearby records, and this is of course a moving target.

My guess is that it is virtually technically impossible do do this with a set based logic and pure sql statements. I'm sure someone could come up with a set of sql that might do the job, but it would be extremely difficult to maintain, much less understand what it does.

So for me, cursors fill the bill very nicely...but it doesn't hurt to put some heavy hardware behind it to ease the pain.
--T

 

[dbomrrsm]

TroyMcClure

Whilst I am one of your:

'anti-cursor' folks

I do agree when you have a situation such as yours there is nothing other to use as a cursor and whilst we all hate them what would we do without them in these situations.

[blue]DBomrrsm[/blue]

 

[donutman]

Troy, I see that you do understand the issues. That's more than most. Let me add a couple of thoughts.
It's not necessary to have a hacker with malicious intent to create a disaster with dynamic SQL. Maybe not likely but given what I've seen of the average user or novice programmer, I wouldn't trust them.
From your description of what you want to do, I'd guess that a solely set-based solution might be difficult to find no less maintain. However, is a dynamic SQL cursor driven SP going to be easy to follow? I'm betting that the T-SQL isn't going to be trivial either.
So maybe your solution is the best, but I would consider using an app especially written for that task. It would probably make the entire process not only more efficient, but also documentation and maintenance would be so much easier. The app could call specially designed SPs to get all the fuzzy logic data that your app needs in order to make the necessary decision.
Sure it takes more time to do this. But consider how much CPU time will be saved over the years that this might run once you have coded it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]