Problem with Select Statement

Question

Post reply

Problem with Select Statement

ezhil-649633

SSC Eights!

Points: 975
More actions
April 29, 2009 at 11:25 pm

#220691

Hi all,
I want to set where condition for the select statement only if the param for procedure is not null
For example:
CREATE PROCEDURE spg_Proc(@ID INT)
AS
BEGIN
--SELECT Statement with where condition if @ID not null
--SELECT Statement without where condition if @ID is null
END
I know we can check this with IF statement and do the desired operation, but i want to check the param is null or not within the SELECT statement itself, And add the condition to the select statement based on the param value.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this sp

create procedure XYZ (@A int)

as

begin

1) Select * from table

where

and @a is null or @a = ''

2) Select * from table

where

and @a is not null or @a ''

3) Select * from table

where /*col1*/ = (case when (@A is null) or( @a = '') then Col1 else @a end)

END

ARUN SAS

ezhil-649633 SSC Eights! Points: 975 More actions · Answer 2

ezhil-649633

SSC Eights!

Points: 975

April 30, 2009 at 1:25 am

#986467

Thanks arun.

Your third option works for me.:-)

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

if IsNull(@ID,0) 0

Select ... where col = @ID

else

Select ...

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

ezhil (4/30/2009)
Thanks arun.
Your third option works for me.:-)

The third option can be re-written as

SELECT * FROM table WHERE ( @a IS NULL OR Col1 = @a )

[/CODE]

--Ramesh

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Notice that if your table is big, you'll might have performance issues. Unfortunetly I don’t remember the technical explanation on way using the third option causes a none optimal query plan (but I have a feeling that others will be able to give us a very good explanation). In any case take a look at the small demo that I wrote using AdventureWorks database. If you’ll compare both query plans and statistics I/O, you’ll see that when you use if statement, you’ll get a better query plan.

create proc FindOrderDetail (@ProductID int)

as

select * from sales.SalesOrderDetail

where (ProductID = @ProductID or @ProductID is null)

go

create proc FindOrderDetail2 (@ProductID int)

as

if @ProductID is null

select * from sales.SalesOrderDetail

else

select * from sales.SalesOrderDetail where ProductID = @ProductID

go

set statistics io on

exec FindOrderDetail 10

exec FindOrderDetail2 10

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/