Noob: StoredProcedure, null for parameters? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Noob: StoredProcedure, null for parameters?

Author

Topic

kaepten
Starting Member

2 Posts

Posted - 2006-06-27 : 07:36:48

I know it's unbelievable, but i didn't ever use SP before. Sorry for this noobish question, don't beat me for this - please ;-)

In my SELECT Query i like to have some (WHERE) paramteters le's say: ID, NAME and AGE. I allways programmicali generate a WHERE statement in relation to which parameters I really get, because not allwasy i get all the parameters together. This is fine for "normal" Tables.

How does this work with SP? If I don't get NAME, do I set the SP-Parameter NAME to null to unconsider it? Or do I have to make some IF statements in the SP to check, if NAME has some value?

Thanks for a short hint!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 08:11:46

You could set Name to NULL and then check for which parameters in SP that are non-null. Or use COALESCE function or ISNULL function.

IN:
@ID 25
@Name NULL
@Age 38

SELECT * FROM WhatEverTable
WHERE    ID = ISNULL(@ID, ID)
         AND Name = ISNULL(@Name, Name)
         AND Age = ISNULL(@Age, Age)

Peter Larsson
Helsingborg, Sweden

kaepten
Starting Member

2 Posts

Posted - 2006-06-27 : 08:40:45

Thank you very much!

This works fine in my test application. Is this the usual way to work with various parameters?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 08:49:05

or



SELECT * FROM WhatEverTable
WHERE    (ID = @ID or @ID is null)
         AND (Name =@Name or @Name is null)
         AND (Age =@Age or @Age is null)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 09:09:57

quote:
Originally posted by kaepten

Thank you very much!

This works fine in my test application. Is this the usual way to work with various parameters?

More or less, yes. The choice of approach is only the habit of the programmer.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-27 : 10:54:49

quote:
The choice of approach is only the habit of the programmer.

According to the 'gospel' article on this (http://www.sommarskog.se/dyn-search.html), it's not just habit since performance can be affected. That article suggests that the following approach can result in a table scan being avoided (with the right indexes, of course):

SELECT * FROM WhatEverTable
WHERE    (ID = @ID and @ID is not null)
         AND (Name = @Name and @Name is not null)
         AND (Age = @Age and @Age is not null)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 11:39:41

According to Mr Erland Sommarskog himself

quote:
I ran the six test searches in Northgale. On SQL 2005, search_orders_3a ran considerably slower than search_orders_3, whereas on SQL 2000, it [COALESCE] was considerably faster!

Is anyone up for a speed comparison on a million row table?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources