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 «««56789

Writing Dynamic Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, June 3, 2009 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:34 AM
Points: 2, Visits: 14
sunil,

You can use this style too....

set @Gender = isNull('M', '_')
set @Age = isNull(22, 0)

SELECT * FROM test_EmployeeDetails
Where Gender LIKE @Gender AND Age > @Age


Thanks!
Azeem
Post #728359
Posted Wednesday, June 3, 2009 12:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:51 AM
Points: 226, Visits: 119
this does not help if you have nullable fields
Post #728448
Posted Thursday, June 4, 2009 3:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Interesting.....

I can now get multiple index seeks

drop proc test2 
go
create proc test2 @ProductId integer,
@ReferenceOrderID integer,
@Quantity integer
as

select *
from [Production].[TransactionHistory]
where (@productid is not null and @productid = ProductID)
or (@ReferenceOrderID is not null and @ReferenceOrderID = ReferenceOrderID)
--or (@quantity is not null and @quantity = Quantity) Uncomment This to force a scan
go

exec test2 @productid = 790,@ReferenceOrderID = NULL,@Quantity=NULL
go
exec test2 @productid = NULL,@ReferenceOrderID =61197 ,@Quantity=NULL


It seems to seek if ALL the referenced columns are indexed, but uncommenting the Quantity lookup a scan takes place.

Edit : Ive updated my blog http://sqlandthelike.blogspot.com/ with this info




Clear Sky SQL
My Blog
Kent user group
Post #728778
Posted Wednesday, June 10, 2009 4:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:34 AM
Points: 2, Visits: 14
isNull funcation for nullable value ... try then reply
Post #732114
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse