Link to home
Start Free TrialLog in
Avatar of Carla Romere
Carla RomereFlag for United States of America

asked on

Stored Procedure with Parameters and Static Values

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

Open in new window


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

Open in new window

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".

Open in new window

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?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>ALTER PROCEDURE AER_SN_KPI_BPS
>@SHIFTSTART DATETIME = dbo.fn_CurrentShift(GETDATE()),
>@NOW DATETIME = GETDATE()
>AS


For starters, you need parenthesis () to delineate your parameters.

ALTER PROCEDURE AER_SN_KPI_BPS (
@SHIFTSTART DATETIME a= dbo.fn_CurrentShift(GETDATE()),
@NOW DATETIME = GETDATE() )
AS
Avatar of Carla Romere

ASKER

Well, I hadn't seen that in any of the examples I was looking at, so I went ahead and added the parentheses. I get the same error.
ASKER CERTIFIED SOLUTION
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I did not know that I could put declare statements in a stored procedure! I just learned something new - thank you!