Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work :)
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>> try this:
>> create proc myproc
>> param int null
>> as
>> declare @.sql varchar(100)
>> if param is null
>> set @.sql = '...' -- your first select
>> else
>> set @.sql = '...' -- your second select
>> exec (@.sql)
>> go
>
>