April 11, 2013 at 4:13 am
Hi,
I have made a stored procedure with a cursor:
USE [DB_admin1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [rept].[suscribedSystems]
-- Add the parameters for the stored procedure here
@startDate DATETIME,
@endDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@SubIDINT,
@sqlVARCHAR(8000)
DECLARE crSystem CURSOR FOR
SELECT [SystemNum]
,[Date] AS SubStart
,
(SELECT TOP (1) [Date]
FROM [DB_admin1].[dbo].[tblRenewals] REN2
WHERE REN2.[Date] > REN.[Date]
AND REN.SystemNum = REN2.SystemNum
ORDER BY [Date] ASC
) AS SubEnd
FROM [DB_admin1].[dbo].[tblRenewals] REN
WHERE
[Date] <= @startDate
AND
(SELECT TOP (1) [Date]
FROM [DB_admin1].[dbo].[tblRenewals] REN2
WHERE REN2.[Date] > REN.[Date]
AND REN.SystemNum = REN2.SystemNum
ORDER BY [Date] ASC
) > @endDate
AND (SELECT TOP (1) [Date]
FROM [DB_admin1].[dbo].[tblRenewals] REN2
WHERE REN2.[Date] > REN.[Date]
AND REN.SystemNum = REN2.SystemNum
ORDER BY [Date] ASC
) <> '2999-01-01'
ORDER BY SubStart
OPEN crSystem
FETCH NEXT FROM crSystem
INTO @SubId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
'INSERT INTO rept.tblSubscribedSystems
([systemNum]
,[dtuStart]
,[dtuEnd]
,[total])
VALUES
(@SubID
,@startDate
,@endDate
,(SELECT COUNT (*)
FROM rept.tblSubscribedSystems)
)'
--PRINT @sql
EXEC (@sql)
FETCH NEXT FROM crSystem
INTO @SubId
END
CLOSE crSystem;
DEALLOCATE crSystem;
END
When i run the stored procedure
--EXEC [rept].[suscribedSystems] '2009-05-01', '2009-06-01'
I get this error:
Msg 16924, Level 16, State 1, Procedure suscribedSystems, Line 52
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Thank you in advance for your time and help.
April 11, 2013 at 4:17 am
these two parts of your SP:
FETCH NEXT FROM crSystem
INTO @SubId
are doing a FETCH INTO on 1 Parameter, @SubID.
The select statement that your cursor is FOR is bringing back more than 1 column.
You need to ensure that the amount of parameters your doing a FETCH INTO match the amount of columns in your select statement
April 11, 2013 at 4:40 am
Thanks for your reply. I have added
FETCH NEXT FROM crSystem
INTO @SubId, @startDate, @endDate
Now I am getting this error:
Must declare the scalar variable "@SubID".
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.
April 11, 2013 at 5:59 am
You don't need dynamic SQL to overcomplicate this, or a cursor to make it run slowly:
DECLARE @CurrentRowCount INT
SELECT @CurrentRowCount = COUNT(*)
FROM [DB_admin1].[dbo].[tblRenewals]
INSERT INTO rept.tblSubscribedSystems
([systemNum]
,[dtuStart]
,[dtuEnd]
,[total])
SELECT
REN.[SystemNum]
,REN.[Date] AS SubStart
,x.SubEnd
,rn = @CurrentRowCount + ROW_NUMBER() OVER(ORDER BY REN.[Date])
FROM [DB_admin1].[dbo].[tblRenewals] REN
CROSS APPLY (
SELECT TOP (1)
SubEnd = REN2.[Date]
FROM [DB_admin1].[dbo].[tblRenewals] REN2
WHERE REN2.[Date] > REN.[Date]
AND REN.SystemNum = REN2.SystemNum
ORDER BY [Date] ASC
) x
WHERE [Date] <= @startDate
AND x.SubEnd > @endDate
AND x.SubEnd <> '2999-01-01'
ORDER BY REN.[Date]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2013 at 9:18 am
Thank you, will give it a try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply