optional parameters - SQL Server Forums

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

optional parameters

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 16:16:10

i have select that says

select something
where
param = ?

so this will be a user entered parameter and I would like to display everything if nothing was entered or if the value of the parameter is null

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-24 : 16:18:49

make it a stored procedure

Create Proc yourProc
	@param int
AS

select 	something
from	somewhere
where	somecolumn = @param
or	@param is null

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 16:27:48

is there a way to do it in the query because it's a little more complicated than i described. Basically I am using a report program called BIRT that builds a report based on an sql so i want to results based on optional parameters

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-24 : 17:20:01

Can you explain what you mean by: is there a way to do it in the query? Russell gave you a sample query, so I'm not getting how you want to construct the query.

Here is a link that talks about catch-all queries:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 17:32:54

ok well i have a really big complicated select query

in the end i finish with WHERE....

so

WHERE name = ?

and this will be user entered but if the user does not enter anything, it should display all the results. I do not want it to be a stored procedure. Just wondering if there is another way

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources