[SQL Server] Error "Must declare scalar variable ..." when using cursor |
| Error "Must declare scalar variable ..." when using cursor Posted: 16 Mar 2013 09:03 AM PDT Hi,Hope you can help me.Every week KPI's for our HR-dept. have to be generated.The problem with this is that the KPI's from the previous week have to be overwritten by the KPI's from the current week. I'll save you the reason for this, but I tried to solve this using a cursor.The cursor variables store the difference in days between today and 7 days back, 14 days back, 21 days back etc.etc. (and stores the corresponding day and year). So for the current week this day-difference is 0; for the previous week this is 7; 2 weeks ago this is 14, 3 weeks ago this is 21 etc.when I run the query for the current week the current week and all the previous weeks have to be inserted in a table and all the existing weeks in the table have to be deleted. The delete-part of the query is not shown below; only the insert part.when I run the following query I get the error:"Msg 137, Level 15, State 2, Line 99Must declare the scalar variable "@kpi_cursor"."I come accross several topics when I google around, but no solution so far. Probably there is a simple solution to the problem.Hope someone can help me with fixing the error.The query is:[code = "sql"]declare @Created varchar(50)declare @ReferenceDay datedeclare @Type varchar(10)set @Created = getdate()set @ReferenceDay = getdate()set @Type = 'Prognosis'-- Cursor variablesDECLARE @DaysJump intDECLARE @Date varchar(10)DECLARE @DateYear varchar(4)-- End cursor variablesDECLARE @Days intSET @Days = @Days + 7DECLARE kpi_cursor CURSOR FOR SELECT @Days as jump, GETDATE() - @Days as jump_date, YEAR(GETDATE() - @Days) as jump_yearWHERE YEAR(GETDATE() - @Days) >= 2013OPEN kpi_cursorFETCH NEXT FROM kpi_cursor INTO @DaysJump, @Date, @DateYearWHILE @@FETCH_STATUS = 0BEGININSERT INTO Q_KPIs_HRM_Test ( KPI, SubKPI, Type, Yr, RefDate, Week, Weekday, StartDate, EndDate, BV, Value, Syscreated, Syscreator )/* 6. Employees out based on 711-workflow */SELECT 'Employees out' as KPI, '' as SubKPI, @Type as Type, YEAR(a.EndDate) as Yr, /* Year of contract enddate */ CONVERT(VARCHAR(10), @ReferenceDay, 105) as RefDay, (select dbo.udf_GetISOWeekNumberFromDate(a.EndDate)) as Week, DATENAME(DW, @ReferenceDay) as Weekday, CONVERT(varchar(50), (GETDATE() - 6 - @DaysJump), 105) as StartDate, CONVERT(varchar(50), (GETDATE() - @DaysJump), 105) as EndDate, h.costcenter as BV, COUNT(*) as Value, CONVERT(VARCHAR(10), @Created, 105) as Syscreated, '4' as Syscreator FROM Absences aLEFT OUTER JOIN humres h ON a.EmpID = h.res_idWHERE ISNULL(a.hid, 1) > 0 and isnull(h.res_id, 999999) > 5000 and a.Type = 711 and a.Status <> 2 and a.EndDate > GETDATE() - @DaysJumpGROUP BY h.costcenter, a.EndDateUNION ALL/* 7. Employees in based on 500-workflow */SELECT 'Employees in' as KPI, '' as SubKPI, @Type as Type, YEAR(a.StartDate) as Yr, /* Year of contract startdate */ CONVERT(VARCHAR(10), @ReferenceDay, 105) as RefDay, (select dbo.udf_GetISOWeekNumberFromDate(a.StartDate)) as Week, DATENAME(DW, @ReferenceDay) as Weekday, CONVERT(varchar(50), (GETDATE() - 6 - @DaysJump), 105) as StartDate, CONVERT(varchar(50), (GETDATE() - @DaysJump), 105) as EndDate, h.costcenter as BV, COUNT(*) as Value, CONVERT(VARCHAR(10), @Created, 105) as Syscreated, '4' as Syscreator FROM Absences aLEFT OUTER JOIN humres h ON a.EmpID = h.res_idWHERE ISNULL(a.hid, 1) > 0 and isnull(h.res_id, 999999) > 5000 and a.Type = 500 and a.Status <> 2 and a.StartDate > GETDATE() - @DaysJumpGROUP BY h.costcenter, a.StartDateFETCH NEXT FROM @kpi_cursor INTO @DaysJump, @Date, @DateYearENDCLOSE kpi_cursorDEALLOCATE kpi_cursor[/code] |
| Posted: 16 Mar 2013 04:24 AM PDT CREATE TABLE EMPLOYEE (EMP_ID INT PRIMARY KEY,FIRSTNAME nvarchar(100),LASTNAME nvarchar(100),Salary INT,CITY nvarchar(100))insert INTO EMPLOYEE VALUES(2, Monu Rathor, 4789.00, Agra);insert INTO EMPLOYEE VALUES(4, Rahul Saxena, 5567.00, London);insert INTO EMPLOYEE VALUES(5, Prabhat Kumar, 4467.00, Bombay);BEGIN TRANSACTIONDeclare @B varchar(100)SET @B = 'Nodia';Declare @D varchar(100)SET @D = 'Delhi'Update employee SET city =(Case WHEN city = 'Agra' THEN @BWHEN city = 'London' THEN @DELSE 0END)ENDThe error faced was as present in the subject. Please helpI also tried the below way,BEGIN TRANSACTIONUpdate employee SET city =(Case WHEN city = 'Agra' THEN 'Delhi' WHEN city = 'London' THEN 'Noida'ELSE 0END)ENDBUT I CAME ACCROSS: Msg 102, Level 15, State 1, Line 9Incorrect syntax near 'END'.Please HELP |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment