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
