Multiple selects in sp

19 Aug 2005 1:31 AM

John

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

Regards

19 Aug 2005 1:42 AM

vuht2000

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

19 Aug 2005 2:02 AM

John

Ok, I have this sp (attached). Still can't make it work.

Thanks

Regards

Show quote

"vuht2000" <vuht2***@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
>

[attached file: SQLQuery.sql]

19 Aug 2005 4:19 AM

Louis Davidson

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

Show quote

"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" <vuht2***@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
>>
>
>
>