,I am trying to create a cursor in sql2005. I have looked a some examples an noticed the data from the cursor is moved into variables, why is this done, is the data not accessible from the cursor? Thanks JW
The cursor holds the data, but in order to access it for whatever it is that you hope to do with it, traditionally people load each line of the cursor into a set of variables. The FETCH operation returns a result set. If you don't FETCH INTO the set of variables, while you'll have a result set, there's not much you can do with it beyond return it to the calling client. Usually people expect to do things with the data which is why they set up a cursor in the first place. On a side note, be sure you need a cursor. They come with significantly higher overhead than a T-SQL statement or even a set of T-SQL statements. There may be better ways to solve whatever problem you're attempting to resolve with this cursor.
I have created my code, but find I am missing an output record. Any help in determining why the record is missed on the output would be greatly appreciated! The record I am missing is: '0000010463 OV02 2013-03-01 00:13:20.000 2013-03-07 08:41:14.000' Below is my input test data and my code. Thanks JW Input Data ---------- bln umachine start_date end_date ---------- ---------- ----------------------- ----------------------- 0000010312 W4 2013-01-22 21:51:06.000 2013-02-14 09:33:44.000 0000010463 OV02 2013-03-01 00:13:20.000 2013-03-07 08:41:14.000 0000010666 OV04 2013-03-07 00:00:00.000 2013-03-07 07:07:22.000 0000010667 W1 2013-04-11 00:00:00.000 2013-04-11 13:27:49.000 0000010669 OV09 2013-04-12 00:00:00.000 2013-04-12 07:29:06.000 0000010673 HGRC1 2013-04-15 00:00:00.000 2013-04-18 03:37:32.000 0000010786 OV06 2013-12-11 01:17:46.000 2013-12-11 12:53:46.000 Script ------ declare @StartDate varchar (8) declare @EndDate varchar (8) set @StartDate = '20130101' set @endDate = '20131231' -- declare @BLN_VAR VARCHAR(10) declare @UMACHINE_VAR Char (10) declare @START_DATE_VAR DATETIME declare @END_DATE_VAR DATETIME declare @Year_Number_VAR Integer declare @WK_Number_VAR Integer -- declare @StartYR Char(4) declare @StartWK Char(2) declare @EndYR Char(4) declare @EndWK Char(2) -- declare perf_data cursor for select BLN, UMACHINE, START_DATE, END_DATE, Year_Number, WK_Number from [tempdb..##Performance_Data_table] order by START_DATE, END_DATE open perf_data FETCH NEXT from perf_data into @BLN_VAR, @UMACHINE_VAR, @START_DATE_VAR, @END_DATE_VAR, @Year_Number_VAR, @WK_Number_VAR WHILE @@FETCH_STATUS = 0 begin set @StartYR = convert(char(4),datepart(year,@START_DATE_VAR)) set @StartWK = (select WeekNumber from X_Week_Control_Table where @StartYR = [Year] and @START_DATE_VAR >= Week_StartDT and @START_DATE_VAR <= week_EndDT) set @EndYR = (select convert(char(4),datepart(year,@END_DATE_VAR))) set @EndWK = (select WeekNumber from X_Week_Control_Table where @EndYR = [Year] and @END_DATE_VAR >= Week_StartDT and @END_DATE_VAR <= week_EndDT) -- ip records select @BLN_VAR, @UMACHINE_VAR, @StartWK,@EndWK while not (@StartWK > @EndWK) begin -- op records select @BLN_VAR, @UMACHINE_VAR, @START_DATE_VAR, @END_DATE_VAR, @Year_Number_VAR, @WK_Number_VAR, @StartWK,@EndWK set @StartWK = (@StartWK + 1) end FETCH NEXT from perf_data into @BLN_VAR, @UMACHINE_VAR, @START_DATE_VAR, @END_DATE_VAR, @Year_Number_VAR, @WK_Number_VAR end CLOSE perf_data DEALLOCATE perf_data