SQL Syntax Error

Posted on 2008-11-06
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?

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

	@par_YR smallint,

	@par_MTH smallint


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

Question by:Mahonek
    1 Comment
    LVL 9

    Accepted Solution

    you can either create a view without those parameters or create a function or procedure with these 2 parameters with returning the same resultset

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now