Avatar of Carla Romere
Carla Romere
Flag 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?
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Carla Romere

8/22/2022 - Mon
Jim Horn

>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
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
Rainer Jeschor

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Carla Romere

ASKER
I did not know that I could put declare statements in a stored procedure! I just learned something new - thank you!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck