How to separate a referenced parameter in an If clause in a Stored Procedure ?

Posted on 2005-05-10
Last Modified: 2012-05-05
How would you rewrite the concatenated string for @SQL in the If clause that follows to separate the Parameter @RptYear so that it is not inside
quotes ? I believe I would get an error message that @RptYear is an undeclared variable if I tried executing the following Stored Procedure with the parameters the way the concatenated sql string is currently written.

@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200)

DECLARE @SQL  varchar(8000)

If @Prop = 'ALL'
     SET @SQL = @SQL + '  AND ((C.DateLost <= CAST((@RptYear - tblStatesAll.MutualFS) AS VARCHAR) + ''12-31'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
Question by:zimmer9
    LVL 17

    Accepted Solution

      SET @SQL = @SQL + '  AND ((C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - tblStatesAll.MutualFS) AS VARCHAR) + ''-12-31'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  

    I think it should be ''-12-31'
    LVL 17

    Expert Comment

    I mean
    I think it should be ''-12-31"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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!

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now