[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-10
2
Medium Priority
?
345 Views
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.

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) '  
0
Comment
Question by:zimmer9
  • 2
2 Comments
 
LVL 17

Accepted Solution

by:
mokule earned 2000 total points
ID: 13974034
  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
 
LVL 17

Expert Comment

by:mokule
ID: 13974049
I mean
I think it should be ''-12-31"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 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