Stored procedure parameter - set default ALL

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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored procedure parameter - set default ALL

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 14:11:31

How do I set the default of a parameter in a stored procedure to get ALL records if the parameter is not supplied?

Duane

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-08-12 : 14:29:27

In your stored procedure you can declare the default for the parameter to be NULL and then check within the stored procedure for a NULL value and retrieve all records when the NULL is passed.

Some days you're the dog, and some days you're the fire hydrant.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-12 : 14:34:04

[code]
Create Proc sp
@param int = null
AS

SELECT *
FROM yourTable
WHERE col1 = @param
OR
@param is NULL[/code]

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 14:48:57

Thanks for the replies. I have a question for each of you:
Skorch: Would I use a case statement to examine whether the parameter is null or not? Could you provide a quick example?
Russel: Doesn't your example return ALL records regardless of what is in @param? If not, please explain.

Duane

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-12 : 15:03:30

no. if you pass in a value, it is used to search. if you don't, it defaults to null.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-12 : 15:07:04

here is a simple test so u can see it in action:


use tempdb;
go

create table t1 (a int, b int)
go
insert t1
select 1, 2 union all
select 2, 2 union all
select 3, 2 union all
select 4, 2 union all
select 5, 2
go

Create Proc usp_test
	@a int = null
AS

select	*
from	t1
where	a = @a or @a is null
GO

EXEC usp_test; -- returns all records
EXEC usp_test @a = 2 -- returns 1 record


drop proc usp_test
drop table t1

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 15:10:19

Thank you, Russel. This is a good, clear example.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources