Dynamic query, local cursor variable and global cursors

Hi all.

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