Optional parm in SP - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Optional parm in SP

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-29 : 09:35:07

is this correct if i dont want to have to define each parameter in a sp:

CREATE proc aa_audit
@name varchar(75) = null,
@date datetime = null,
@accountnumber varchar(11) = null
as
select username, entered_at, account, operation, aa_model, aa_rule, aa_desc
from AA_AuditTable
where (username = @name) or (entered_at = @date) or account = @accountnumber
GO

I want to give the user options on what they want to search on i guess.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-29 : 09:45:47

Also you need to pass Null if you dont want pass Values to the parameter

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-29 : 09:53:06

If you specify NULL as default value, you won't get proper result if any of the parameters not supplied...You have to write query like this:

select username, entered_at, account, operation, aa_model, aa_rule, aa_desc
from AA_AuditTable
where (@name is NULL or username = @name) and
      (@date is NULL or entered_at  = @date) and
      (@accountnumber is NULL or account = @accountnumber)

Harsh Athalye
India.
"Nothing is Impossible"

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-09-29 : 10:09:21

You can also use coalesce:

@name varchar(75) = null,

where (username = COALESCE(@name,username)

it takes the first one with a value so if @name = null you get where username = username, and it will return all

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-29 : 10:35:00

YOU GUYS ALL ROCK, APPRECIATE THE HELP

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-29 : 11:51:21

With the following now, i dont get any results back:

CREATE proc aa_audit
@name varchar(75),
@date datetime,
@accountnumber varchar(11)
as
select username, entered_at, account, operation, aa_model, aa_rule, aa_desc
from AA_AuditTable
where (@name is NULL or username = @name) and
(@date is NULL or entered_at = @date) and
(@accountnumber is NULL or account = @accountnumber)

lets say i do

exec SPNAME 'Duane Haas','',''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources