CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Danger_Fox
Danger_Fox

Reputation: 449

Issue with datetime variable in sql server 2008

I'm trying to declare a date variable in SQL and when I debug it, it hangs on the SET part. There is more to the query, but it essentially loops through, adding different inputs until it hits December 31st.

DECLARE @ShiftDate AS Datetime
BEGIN
    SET @ShiftDate = '2013-01-01 00:00:00.000'
END
DECLARE @ShiftDate AS DATETIME
BEGIN
SET @ShiftDate = '2013-01-01 00:00:00.000'
END

DECLARE @EndDate AS DATETIME
BEGIN
SET @EndDate = '2013-12-31 00:00:00.000'
END


GO
WHILE @ShiftDate <= @EndDate
BEGIN
INSERT INTO [ManufacturingTracking].[dbo].[TenteringCrewShift]
       ([SearchDate]
       ,[Shift])
 VALUES (@ShiftDate, 'B')
 SET @ShiftDate = DATEADD(DATE, 1, @ShiftDate)
 IF @ShiftDate > @EndDate
    BREAK
 ELSE
    CONTINUE
END

And if I tell it to continue on it gives the error:

Must declare the scalar variable "@ShiftDate".

Any help is appreciated.

Upvotes: 0

Views: 172

Answers (1)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

Remove GO from your script; the GO is essentially starting a new section of processing, and @ShiftDate won't be in scope in the next section of your script.

See the MSDN documentation on GO for more information.

Upvotes: 4

Related Questions