I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.
So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.
The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:
SET @.sqlQuery = ... (build the dinamic sql query)
SET @.cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @.sqlQuery
EXEC @.cursorQuery
OPEN myCursor
FETCH NEXT FROM myCursor INTO ...
CLOSE myCursor
DEALLOCATE myCursor
This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.
My first thought was to make the cursor name unique, which led me to:
...
SET @.cursorName = 'myCursor' + @.uniqueUserID
SET @.cursorQuery = 'DECLARE '+ @.cursorName + 'CURSOR FAST_FORWARD FOR ' + @.sqlQuery
EXEC @.cursorQuery
...
The problem with this is that I can't do a FETCH NEXT FROM @.cursorName since
@.cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.
So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.
I guess my concrete questions are:
Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?
Anybody sees another way arround this?Thanks in advance,
Carlos
First off, let me just say that I kind of hate myself for the answer I am going to give you, because almost certainly the processing you are trying to do with a cursor in SQL could be done easier/better/faster/etcer outside of T-SQL, and you would be far happier with the final result.
On the other hand, I am pretty sure there is a way to do this, using sp_executesql by passing a parameter of type cursor to the proc:
declare @.query nvarchar(max), @.number int, @.mainCursor cursor
set @.query = ' set @.cursor = cursor for select 1 as number
open @.cursor'
exec sp_executesql @.query,N'@.cursor cursor output',@.mainCursor output
fetch next from @.maincursor into @.number
select @.number
Good luck with this, but seriously consider doing this outside of T-SQL :)
|||Hey Louis.I can only say I was amazed to see that your piece of code worked,
since I had tried about the same thing a while ago and it didn't work.
So I did a little digging to see what the difference was between your
implementation and mine. And then I discovered something odd: if you
put the OPEN @.cursor after the sp_executesql command, instead of in it, you get an error saying that your variable has no cursor allocated to it. Go figure.
Well, I guess this is part of why you're telling me to give up T-SQL.
Believe me, I'm no masochist. I know this would be much easier if I did
it on the webserver's side, where I have a beautiful JVM eager to do
the job. But I have to disagree when you say it would be faster.
The trouble is that I have to go through a great amount of data to
display but a few lines of result to the user. The overhead involved in
transfering all this data to another system is just too great (it's a
web application, so the time scale is very short: a few seconds will be
enough to hamper it). So I guess I'll have to live with it, right?
Anyways, many thanks for your help. Problem solved. Moving on.
Carlos
