Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Select Statement Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 11:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 13, 2010 2:23 AM
Points: 129, Visits: 236
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.


Post #707401
Posted Wednesday, April 29, 2009 11:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
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
Post #707413
Posted Thursday, April 30, 2009 1:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 13, 2010 2:23 AM
Points: 129, Visits: 236
Thanks arun.
Your third option works for me.
Post #707447
Posted Thursday, April 30, 2009 2:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:48 AM
Points: 3,241, Visits: 5,002
if IsNull(@ID,0) <> 0
Select ... where col = @ID
else
Select ...



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

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #707479
Posted Thursday, April 30, 2009 3:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
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 )



--Ramesh

Post #707497
Posted Thursday, April 30, 2009 3:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #707510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse