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

Dynamic Cross Tab Using Cursors.

I have two tables
1)Rollout_detail
start_date Datetime,
contract_date Datetime,
budget_amt Money
store_id int(foriegn key referring store.store_id)
pan_number varchar(20)
roll_id int

2)store
store_id int(primary key)
skey varchar(10)

these two tables are tied with store_id
and in rollout_detail there can be many pan_numbers for a given
store.(pan_number + store ) are unique.

Now here is the problem.

I need to generate a cross tab report with
store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
pan2_Contract_date,pan2_budget_amt,pan2_start_date and so on.

I tried this with in a procedure.
------------------
CREATE PROCEDURE crosstab
@.roll_id INT
WITH ENCRYPTION
AS
DECLARE @.sql VARCHAR(8000),@.panNumber VARCHAR(20)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
FROM rollout_detail WHERE roll_id=@.roll_id
OPEN al_cursor
SELECT @.sql='SELECT skey, '
FETCH NEXT FROM al_cursor INTO @.panNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.sql=@.sql+'(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN start_date END) as
P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN budget_amt END) as
P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN contract_date END) as
P'+rtrim(@.panNumber)+'_cd,'

FETCH NEXT FROM al_cursor INTO @.panNumber
END
SELECT @.sql=left(@.sql, len(@.sql)-1)+' '
SELECT @.sql=@.sql+'FROM rollout_detail rd,store s
WHERE rd.store_id=s.store_id AND roll_id='+cast(@.roll_id as varchar)

EXEC (@.sql)
CLOSE al_cursor
DEALLOCATE al_cursor
SET ANSI_WARNINGS ON

EXECUTE crosstab 1
------------------
I am getting multiple records for the same store
the result set is.

skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
1 12/2/04 400.0 3/4/05 NULL NULL NULL
1 NULL NULL NULL 5/6/04 566.00 3/4/04

I want the result set merged for each store.

Please help,

Thanks a million

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Jaidev Paruchuri" <jaip26@.hotmail.com> wrote in message
news:408fbf3d$0$200$75868355@.news.frii.net...
> I have two tables
> 1)Rollout_detail
> start_date Datetime,
> contract_date Datetime,
> budget_amt Money
> store_id int(foriegn key referring store.store_id)
> pan_number varchar(20)
> roll_id int
> 2)store
> store_id int(primary key)
> skey varchar(10)
> these two tables are tied with store_id
> and in rollout_detail there can be many pan_numbers for a given
> store.(pan_number + store ) are unique.
> Now here is the problem.
> I need to generate a cross tab report with
> store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
> pan2_Contract_date,pan2_budget_amt,pan2_start_date and so on.
> I tried this with in a procedure.
> ------------------
> CREATE PROCEDURE crosstab
> @.roll_id INT
> WITH ENCRYPTION
> AS
> DECLARE @.sql VARCHAR(8000),@.panNumber VARCHAR(20)
> SET NOCOUNT ON
> SET ANSI_WARNINGS OFF
> DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
> FROM rollout_detail WHERE roll_id=@.roll_id
> OPEN al_cursor
> SELECT @.sql='SELECT skey, '
> FETCH NEXT FROM al_cursor INTO @.panNumber
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.sql=@.sql+'(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN start_date END) as
> P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN budget_amt END) as
> P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN contract_date END) as
> P'+rtrim(@.panNumber)+'_cd,'
> FETCH NEXT FROM al_cursor INTO @.panNumber
> END
> SELECT @.sql=left(@.sql, len(@.sql)-1)+' '
> SELECT @.sql=@.sql+'FROM rollout_detail rd,store s
> WHERE rd.store_id=s.store_id AND roll_id='+cast(@.roll_id as varchar)
> EXEC (@.sql)
> CLOSE al_cursor
> DEALLOCATE al_cursor
> SET ANSI_WARNINGS ON
> EXECUTE crosstab 1
> ------------------
> I am getting multiple records for the same store
> the result set is.
> skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
> 1 12/2/04 400.0 3/4/05 NULL NULL NULL
> 1 NULL NULL NULL 5/6/04 566.00 3/4/04
>
> I want the result set merged for each store.
> Please help,
> Thanks a million
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You're probably looking for something like this - using MAX() and GROUP BY:

select skey,
max(case when ...) as A,
max(case when ...) as B,
...
from
...
where
...
group by skey

Simon|||Simon,

That worked.
Thankyou very much for the solution.
Though it was simple,I was thinking about alternate solutions.I really
appreaciate you guys.

--Jay

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!