?
Solved

SQL Syntax Error

Posted on 2008-11-06
1
Medium Priority
?
300 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Mahonek
1 Comment
 
LVL 9

Accepted Solution

by:
jamesgu earned 375 total points
ID: 22901377
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question