I have a a sql query that works beautifully. It pulls all the data that I need.
DECLARE @SHIFTSTART DATETIME
DECLARE @NOW DATETIME
SET @SHIFTSTART = dbo.fn_CurrentShift(GETDATE())
SET @NOW = GETDATE()
SELECT TOP ( 100 ) PERCENT [DateTime], TagName, 1 AS QTY,
dbo.fn_CurrentShift(GETDATE()) AS SHIFTSTART, GETDATE() AS TIMENOW
WHERE TagName IN ('SN_L1_PE_Product_At_Length',
AND ( DateTime BETWEEN @SHIFTSTART AND @NOW)
GROUP BY DATETIME, TagName
However, I can't use that as a view. So I want to make that a stored procedure, but I keep getting an error and for the life of me I don't see where the problem is. This is my stored procedure:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE AER_SN_KPI_BPS
@SHIFTSTART DATETIME = dbo.fn_CurrentShift(GETDATE()),
@NOW DATETIME = GETDATE()
SET NOCOUNT ON;
SELECT TOP (100) PERCENT [DateTime], TagName, Value AS QTY
WHERE TagName IN (
AND [DateTime] BETWEEN @SHIFTSTART AND @NOW
When I try to execute that to save the stored procedure, I get this error:
Msg 102, Level 15, State 1, Procedure AER_SN_KPI_BPS, Line 3
Incorrect syntax near '.'.
Msg 137, Level 15, State 2, Procedure AER_SN_KPI_BPS, Line 20
Must declare the scalar variable "@SHIFTSTART".
I've tried just putting the values into the query itself, but the database that it's querying will then only return values for the last hour and not since the beginning of the shift. It's a database that our plant uses on the controls side. If I declare them like in the first query, it pulls all the data accurately. What am I doing wrong on the stored procedure?