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)
June 22, 2006 at 1:35 pm
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) )
June 22, 2006 at 3:06 pm
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.?
June 22, 2006 at 3:08 pm
>>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 ?
June 22, 2006 at 3:30 pm
is too long for me to write and some of the information cannot be disclosed. I think the information I provided is sufficient
June 22, 2006 at 3:30 pm
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.
June 22, 2006 at 3:30 pm
is too long for me to write and some of the information cannot be disclosed. I think the information I provided is sufficient
June 23, 2006 at 12:36 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply