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

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.

CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200)

AS
DECLARE @SQL  varchar(8000)

If @Prop = 'ALL'
   BEGIN
     SET @SQL = @SQL + '  AND ((C.DateLost <= CAST((@RptYear - tblStatesAll.MutualFS) AS VARCHAR) + ''12-31'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
zimmer9Asked:
Who is Participating?
 
mokuleCommented:
  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'
0
 
mokuleCommented:
I mean
I think it should be ''-12-31"
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.