Brug af cursor i Stored Procedure
HejJeg har lavet en stored procedure som desværre returnerer TO resultset. Jeg kan så ikke hive dataene frem i C#.
Det skyldes brug af cursor. Min sql er lidt lang og sat på til allersidst. Her kommer brudstykker, som jeg mener er forklaring, me for fuldstændighedens skyld kan hele min sp læses.
1) jeg fylder en temp-table op med data
-- Populate #TempTable WITH open activities
SELECT
LastActivityDetail.LastTimeStamp
, Status.StatusText
, Queues.QueueName
, ActivityDetails_1.StatusID
, ActivityDetails_1.QueueID
, convert(varchar(10), LastTimeStamp, 110) as nigger
INTO #TempTable
2) laver en cursor (og her kommer det første resultset, pg.r. select).
DECLARE @cursorlts varchar(10)
DECLARE Status_Cursor CURSOR FOR
SELECT DISTINCT convert(varchar(10), LastTimeStamp, 110)
FROM #TempTable
3) kører cursoren ind i endnu en tabel:
OPEN Status_Cursor;
FETCH NEXT FROM Status_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Status_Cursor
INTO @cursorlts
INSERT INTO #TMP2( QueueName, StatusText, StatusID, Antal, LastTime)
select
QueueName
, StatusText
, StatusID
, COUNT(StatusText) as Antal
, @cursorlts
from #TempTable
where convert(varchar(10), LastTimeStamp, 110) = @cursorlts
-- where convert(varchar(10), LastTimeStamp, 110) = @cursorlts
group by QueueName, StatusText, StatusID
3) resultatet som jeg ønsker ligger i temp2 -tabellen:
SELECT * FROM #TMP2
jeg får som resultat:
(No column name)
11-04-2014
QueueName StatusText StatusID Antal LastTime
FSC Indlevering 7 8 2014-11-05 00:00:00.000
FSC WCM opfølgning Indlevering 7 1 2014-11-05 00:00:00.000
altså TO sæt data som jeg ikke kan finde ud af at få fat i. Kan nogle hjælpe mig med hvordan man gør?
Hele Stored Procedure:
ALTER PROCEDURE [dbo].[hah_OpenActivities]
@DateFrom DATETIME = null
, @DateTo DATETIME = null
, @ALL BIT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
IF OBJECT_ID (N'#TMP2', N'U') IS NOT NULL
DROP TABLE #TMP2
CREATE TABLE #TMP2(
QueueName VARCHAR(50)
, StatusText VARCHAR(30)
, StatusID SMALLINT
, Antal SMALLINT
, LastTime DATETIME
)
-- Populate #TempTable WITH open activities
SELECT
-- Activities.ActivityID
-- , Vendors.VendorName
-- , Products.ProductName
LastActivityDetail.LastTimeStamp
, Status.StatusText
-- , Activities.Vip
, Queues.QueueName
, ActivityDetails_1.StatusID
, ActivityDetails_1.QueueID
-- , Activities.VendorID
-- , Activities.ProductID
-- , LastActivityDetail.FirstTimeStamp
-- , Activities.ActivityStartDateTime
-- , Activities.ProductReceivedDate
-- , Teams.TeamName
-- , Activities.CustomerName
-- , Activities.ContactName
-- , Partners.PartnerName
-- , Activities.VendorReferenceNumber
-- , Activities.SerialNumber
, convert(varchar(10), LastTimeStamp, 110) as nigger
INTO #TempTable
FROM Teams LEFT OUTER JOIN
Employees ON Teams.TeamID = Employees.TeamID RIGHT OUTER JOIN
Vendors INNER JOIN
Activities ON Vendors.VendorID = Activities.VendorID INNER JOIN
Products ON Activities.ProductID = Products.ProductID INNER JOIN
(SELECT ActivityID, MAX(ActivityDetailID) AS ActivityDetailID, MIN(TimeStamp) AS FirstTimeStamp, MAX(TimeStamp) AS LastTimeStamp
FROM ActivityDetails
GROUP BY ActivityID) AS LastActivityDetail ON Activities.ActivityID = LastActivityDetail.ActivityID INNER JOIN
ActivityDetails AS ActivityDetails_1 INNER JOIN
Status ON ActivityDetails_1.StatusID = Status.StatusID INNER JOIN
Queues ON ActivityDetails_1.QueueID = Queues.QueueID ON LastActivityDetail.ActivityDetailID = ActivityDetails_1.ActivityDetailID ON
Employees.EmployeeID = Queues.EmployeeID LEFT OUTER JOIN
Partners ON Activities.PartnerID = Partners.PartnerID
WHERE (Activities.ActivityClosed = 0 )
--and convert(varchar(10), LastTimeStamp, 110) = '11-11-2014'
TRUNCATE TABLE #TMP2
DECLARE @cursorlts varchar(10)
DECLARE Status_Cursor CURSOR FOR
SELECT DISTINCT convert(varchar(10), LastTimeStamp, 110)
FROM #TempTable
-- -First row from temptable to be insertet into tmp2-table
declare @firstLastTimeStamp datetime
set @firstLastTimeStamp = (SELECT TOP 1 convert(varchar(10), LastTimeStamp, 110) FROM #TempTable ORDER BY LastTimeStamp ASC)
INSERT INTO #TMP2( QueueName, StatusText, StatusID, Antal, LastTime)
select
QueueName
, StatusText
, StatusID
, COUNT(StatusText) as Antal
, @firstLastTimeStamp
from #TempTable
where convert(varchar(10), LastTimeStamp, 110) = @firstLastTimeStamp
group by QueueName, StatusText, StatusID
OPEN Status_Cursor;
FETCH NEXT FROM Status_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Status_Cursor
INTO @cursorlts
INSERT INTO #TMP2( QueueName, StatusText, StatusID, Antal, LastTime)
select
QueueName
, StatusText
, StatusID
, COUNT(StatusText) as Antal
, @cursorlts
from #TempTable
where convert(varchar(10), LastTimeStamp, 110) = @cursorlts
-- where convert(varchar(10), LastTimeStamp, 110) = @cursorlts
group by QueueName, StatusText, StatusID
END;
CLOSE Status_Cursor;
DEALLOCATE Status_Cursor;
SELECT * FROM #TMP2
-- clean up
DROP TABLE #TMP2;
DROP TABLE #TempTable
END
