troubleshooting Question

Stored Procedure with Parameters and Static Values

Avatar of Carla Romere
Carla RomereFlag for United States of America asked on
Microsoft SQL Server 2005
4 Comments1 Solution466 ViewsLast Modified:
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
FROM         History
WHERE TagName IN ('SN_L1_PE_Product_At_Length',
'SN_L2_PE_Product_At_Length',
'SN_L3_PE_Product_At_Length',
'SN_L4_PE_Product_At_Length')
AND Value=1
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
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE AER_SN_KPI_BPS

@SHIFTSTART DATETIME = dbo.fn_CurrentShift(GETDATE()),
@NOW DATETIME = GETDATE()

AS
	
SET NOCOUNT ON;
BEGIN

SELECT TOP (100) PERCENT [DateTime], TagName, Value AS QTY
FROM History
WHERE TagName IN (	
		'SN_L1_PE_Product_At_Length',
		'SN_L2_PE_Product_At_Length',
		'SN_L3_PE_Product_At_Length',
		'SN_L4_PE_Product_At_Length'
		 )
AND Value=1
AND [DateTime] BETWEEN @SHIFTSTART AND @NOW
END
GO
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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros