SQL Syntax Error

Hello programmers.  I am getting the following error to the following SQL script.  Can someone help me identify what it is in my syntax (specifically the variables) that is incorrect?  Can I even use variables when creating a view?

ERROR:
Server: Msg 170, Level 15, State 1, Procedure TopLevel, Line 15
Line 15: Incorrect syntax near '@par_YR'.
Server: Msg 137, Level 15, State 1, Procedure TopLevel, Line 35
Must declare the variable '@par_YR'.
CREATE VIEW TopLevel
 
	@par_YR smallint,
	@par_MTH smallint
 
AS
 
SELECT DISTINCT oas_reppandl_test.cmpcode, oas_reppandl_test.unit, oas_reppandl_test.account, oas_reppandl_test.year, 
oas_reppandl_test.period, oas_reppandl_test.pnl_type, Sum(oas_reppandl_test.Act_Amt) AS SumOfAct_Amt, 
Sum(oas_reppandl_test.Act_Base) AS SumOfAct_Base, Sum(oas_reppandl_test.Act_Basens) AS SumOfAct_Basens, 
Sum(oas_reppandl_test.Bud_Amt) AS SumOfBud_Amt, Sum(oas_reppandl_test.Bud_Base) AS SumOfBud_Base, 
Sum(oas_reppandl_test.Bud_Basens) AS SumOfBud_Basens, Sum(oas_reppandl_test.LY_Amt) AS SumOfLY_Amt, 
Sum(oas_reppandl_test.LY_Base) AS SumOfLY_Base, Sum(oas_reppandl_test.LY_Basens) AS SumOfLY_Basens, 
Sum(oas_reppandl_test.ActYTD_Amt) AS SumOfActYTD_Amt, Sum(oas_reppandl_test.ActYTD_Base) AS SumOfActYTD_Base, 
Sum(oas_reppandl_test.ActYTD_Basens) AS SumOfActYTD_Basens, Sum(oas_reppandl_test.BudYTD_Amt) AS SumOfBudYTD_Amt, 
Sum(oas_reppandl_test.BudYTD_Base) AS SumOfBudYTD_Base, Sum(oas_reppandl_test.BudYTD_Basens) AS SumOfBudYTD_Basens, 
Sum(oas_reppandl_test.LYYTD_Amt) AS SumOfLYYTD_Amt, Sum(oas_reppandl_test.LYYTD_Base) AS SumOfLYYTD_Base, 
Sum(oas_reppandl_test.LYYTD_Basens) AS SumOfLYYTD_Basens
FROM oas_reppandl_test
GROUP BY oas_reppandl_test.cmpcode, oas_reppandl_test.unit, oas_reppandl_test.account, oas_reppandl_test.year, 
oas_reppandl_test.period, oas_reppandl_test.pnl_type
HAVING ((oas_reppandl_test.cmpcode='fornaio') AND (oas_reppandl_test.unit='002999' Or oas_reppandl_test.unit='003999') 
AND (oas_reppandl_test.account Between '40000' And '49999') AND (oas_reppandl_test.year=@par_YR) 
AND (oas_reppandl_test.period=@par_MTH)AND (oas_reppandl_test.pnl_type)="M");

Open in new window

MahonekAsked:
Who is Participating?
 
jamesguConnect With a Mentor Commented:
you can either create a view without those parameters or create a function or procedure with these 2 parameters with returning the same resultset
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.