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
