Pass Variable into a Dynamic SQL with a Union Statement

[fredong]

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 '

 

[JMCraig]

Hi fredong,

This isn't difficult to do (from what I can tell), but the syntax you've got needs a fair amount of adjustment--and you can't do Dynamic SQL in this context. Whatever system you're migrating this from, be aware that in Sybase stored procedures (and SQL in general), you cannot represent a column or table name with a variable.

So, read the section of the Sybase Transact-SQL Users Guide on stored procedures (all the Sybase manuals are on the web and their docs are a big cut above average in quality). Here's one way to get what you have in mind (although there's no Sybase DATE type [unless you've created your own]), so I've used datetime. Also, you don't have anything creating the #Profit table, but I assume that's handled before this procedure is called.

It doesn't appear to me that you need the UNION operator at all--that's only useful when combining data from multiple tables (which isn't the case in your example); I've used the IN operator in the WHERE clause instead.


[tt]
create proc sales_by_date( @searchDate datetime, @mode char( 1 ) )
as
if @mode = 'R'
insert into #Profit( Items, Amt )
select Items, Amt
from Sales
where State in ( 'SC', 'TX' )
and ReturnDate = @searchDate

else if @mode = 'S'
insert into #Profit( Items, Amt )
select Items, Amt
from Sales
where State in ( 'SC', 'TX' )
and SalesDate = @searchDate

/* maybe have another else with a RAISERROR here? */
return
[/tt]

I think that captures the essence of what you're after. Two principles that are useful to know: 1) there's no such thing as "dynamic SQL" in a stored procedure declaration; 2) you cannot ever use a local variable to represent a table or column or other object (such as a stored procedure name). A stored procedure has to be something that can be compiled with all object/column references known at declaration time.

You could, of course, code up a program to create SQL and have it pass it to the database, but you would not do that in the form of a stored procedure typically. Why not? Because if you're generating SQL directly, you're generally doing that because you cannot simply call a procedure with different parameters.

Note that a common, useful pattern for what you've done with the #Profit table is this:

[tt]
create procedure /* name and arguments */
as
select /* columns */
into #Profit
from Sales
where /* conditions */

/* add other stuff to the table or manipulate its contents */

select * from #Profit

drop table #Profit
return
[/tt]

Naturally, whether using a pattern like that is useful depends upon what you need to do. Rather than simply selecting #Profit's data, you could also call another stored procedure first, for instance.

HTH,

John

John Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com

 

[fredong]

Thanks.