Passing 2 Variables into a Dynamic SQL in a Union Statement

Question

Passing 2 Variables into a Dynamic SQL in a Union Statement

SSCrazy

Points: 2890

June 22, 2006 at 1:28 pm

I have a Store procedure with a Dynamic SQL that I want to pass a variable into it with a Union statment. Is that Possible? Below is my Store procedure syntax. Let me know if there is a way to make this work. Thanks.

Create Proc DYSQL @searchDate Date, @Mode Char(1)

As

Declare @sql AS LongVarchar(1000),

Declare @Type Varchar(100)

IF @mode ='R'

Then @Type =' ReturnDate '

END IF

IF @mode ='S'

Then @Type =' SalesDate '

END IF

Set @sql =

'Insert into #Profit( Items, Amt)

Select

Items,

Amt

From Sales

Where State ='SC'

AND @Type = @searchDate

UNION ALL

Select

Items,

Amt

From Sales

Where State ='TX'

AND @Type = @searchDate '

Execute (@SQL)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You do not need dynamic SQL for this. Nor do you need a UNION:

Insert into #Profit( Items, Amt)

Select Items, Amt

From Sales

Where State IN ( 'SC', 'TX' )

AND ((@Mode = 'R' AND ReturnDate = @SearchDate)

OR (@Mode = 'S' AND SalesDate = @SearchDate) )

pcq0125 SSCrazy Points: 2890 More actions · Answer 2

There are more fields than I have showed and some have subquery in on the Union statement. Thus, I definitely need the Union statement. I need this to work and it has to be a Dynamic SQL and a union statement. Any ideas.?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>There are more fields than I have showed and some have subquery in on the Union statement.

Wouldn't it have been better to state that right from the outset, then, so that people don't waste their time constructing replies based off incomplete info ?

pcq0125 SSCrazy Points: 2890 More actions · Answer 4

is too long for me to write and some of the information cannot be disclosed. I think the information I provided is sufficient

pcq0125 SSCrazy Points: 2890 More actions · Answer 5

is too long for me to write and some of the information cannot be disclosed. I think the information I provided is sufficient for my question.

pcq0125 SSCrazy Points: 2890 More actions · Answer 6

is too long for me to write and some of the information cannot be disclosed. I think the information I provided is sufficient

vadba SSChampion Points: 11133 More actions · Answer 7

I would listen to PW, not only does dynamic SQL usually perform poorly, in this case its also very hard to read. Note the example below with all of the single quote marks - really, really hard to read. By the way, you should always use a PRINT before trying to EXECUTE. Make sure the result is correct first, then comment out the PRINT and add the EXEC.

DECLARE @searchDate DateTime

, @sql AS varchar(8000)

, @Type AS varchar(100)

SET @searchDate = GetDate()

SET @Type =' SalesDate '

Set @sql =

'Insert into #Profit( Items, Amt)

Select

Items,

Amt

From Sales

Where State =''SC''

AND ' + @Type + ' = ''' + CONVERT(varchar(25), @searchDate, 113) + '''

UNION ALL

Select

Items,

Amt

From Sales

Where State =''TX''

AND ' + @Type + ' = ''' + CONVERT(varchar(25), @searchDate, 113) + ''''

PRINT @sql