HELP!! - TSQL Cursor problem

Hi,

I have a work project due very soon and am stuck with something.

I am using a cursor in a stored procedure to return the required data for output in an ASP/VBScript page. The problem is that (as run in MS Query Analyser) the stored procedure returns the data in individual data sets - 1 for each iteration of the cursor. It is returned in multiple frames in QA, the same as when you run multiple queries at the same time in the QA window.

I have never used cursors before so maybe this is to be expected - but what I want is for all the data to be returned in one data set. At present I can only access part of the data in my webpage - when I run the stored procedure and loop thru the data in the webpage, there is only the data from the first iteration of the cursor.

Below is a representation of a chunk of rows from the table, the stored procedure and a representation of the returned results. Can you please help me to return all the data in a single data set, or else tell me how I can access each of the data sets in my webpage.

Many thanks in advance for your help

Simon

simon.barnettnospam@.elexon.co.uk

Table

ID_col, Category_col, KeyAccountability_col, PerformanceMeasure_col, StaffID_col
1, Delivery, KeyAcc1, PerfMeas1, 3
3, Delivery, KeyAcc2, PerfMeas2, 3
7, Delivery, KeyAcc3, PerfMeas3, 3
8, Department, KeyAcc4, PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5, 3
12, Department, KeyAcc6, PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7, 3
16, Communications, KeyAcc8, PerfMeas8, 3

Stored Procedure

declare @.var0 nchar(56)
declare @.var1 nchar(56)
declare keyaccscursor cursor for
(SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid = @.jobprofileID)
OPEN keyaccscursor
FETCH NEXT FROM keyaccscursor
INTO @.var1
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct KeyAccountability as col1, 'keyacc' as rowtype from KeyAccountability where (category = @.var1) and (jobprofileid = @.jobprofileID)
union
select distinct category as col1, 'cat' as rowtype from KeyAccountability where (category = @.var1) and (jobprofileid = @.jobprofileID)
FETCH NEXT FROM keyaccscursor
INTO @.var1
END
CLOSE keyaccscursor
DEALLOCATE keyaccscursor

Results (when run in MSSQL Query Analyser )

-

KeyAccountability PerformanceMeasure (column headings)

Delivery

KeyAcc1 PerfMeas1

KeyAcc2 PerfMeas2

KeyAcc3 PerfMeas3

-

KeyAccountability PerformanceMeasure (column headings)

Department

KeyAcc4 PerfMeas3

KeyAcc5 PerfMeas4

KeyAcc6 PerfMeas5

-

KeyAccountability PerformanceMeasure (column headings)

Communications

KeyAcc7 PerfMeas6

KeyAcc7 PerfMeas7

I understand you have to present the following data

3, Delivery, KeyAcc2, PerfMeas2, 3
7, Delivery, KeyAcc3, PerfMeas3, 3
8, Department, KeyAcc4, PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5, 3
12, Department, KeyAcc6, PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7, 3
16, Communications, KeyAcc8, PerfMeas8, 3

As

Delivery

KeyAcc2, PerfMeas2
KeyAcc3, PerfMeas3

And

Department

KeyAcc4, PerfMeas4, 3
KeyAcc5, PerfMeas5, 3

KeyAcc6, PerfMeas6, 3

And so on.

I should use a DropDownList cotrol with sqlsource ?SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid = @.jobprofileID”

And a GridView control with

Select KeyAccountability, PerformanceMeasure from KeyAccountability where Category=@.Categ

And @.categ is linked with DropDownList value.

Hope this help

|||

To fix your query for your requirement,

Code Snippet

declare @.var0 nchar(56)

declare @.var1 nchar(56)

declare @.jobprofileID as int

Set @.jobprofileID = 1

declare @.result table

(

col1 varchar(1000),

rowtype varchar(10)

)

declare keyaccscursor cursor for

(select distinct [category] from [keyaccountability] where jobprofileid = @.jobprofileid)

open keyaccscursor

fetch next from keyaccscursor

into @.var1

while @.@.fetch_status = 0

begin

Insert Into @.result

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where (category = @.var1) and (jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where (category = @.var1) and (jobprofileid = @.jobprofileid)

fetch next from keyaccscursor

into @.var1

end

close keyaccscursor

deallocate keyaccscursor

Select * from @.result

|||

You can achieve the result without cursor also, (Highly recommended)

Code Snippet

declare @.jobprofileID as int

set @.jobprofileID = 1

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

|||

Thanks very much for this - inserting into a table during the loop gives me the single data set - exactly as required.

There are 2 problems though, which may be related.

1.

The first is that I cannot declare a table in memory.

declare @.result table

(

col1 varchar(1000),

rowtype varchar(10)

)

gives me the following error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

(When I change to creating a permanent table using CREATE table, and run it in QA, the correct data populates the table.)

2.

When adding this into my stored procedure and running my webpage, the page breaks with the message that the recordset cannot be looped thru because it is closed.

When I comment out the insert statement but leave in the CREATE statement, the page works as it did before.

If you can help with this I would be very grateful.

Many thanks

Simon

|||

Hi,

I tried this also but although all the required results are returned as a single data set, KeyAccountabilities are not associated with the Categories as required.

All the Categories are listed in the top rows and then the KeyAccountabilities are listed in random order below. Looking at the SQL I can't see a way to group them as required using this method.

Would it be possible?

Many thanks

Simon

declare @.jobprofileID as int

set @.jobprofileID = 1

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

|||

I am really wondering how its happen. What is the version of SQL Server you are using.

To find,

Select @.@.VERSION

Even on your ASP page you can change the RecordSet behaviour as UseClient istead of UseServer. It will fix the issue.

|||

The following query may fix your group/order issue,

Code Snippet

declare @.jobprofileID as int

set @.jobprofileID = 1

Select col1,rowtype from

(

select category,keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

Union

select category,category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

) as Data

Order By

category, Case When rowtype='cat' Then 1 Else 2 End, Col1

|||Thank you

sql