Parameter - 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 2005 Forums

Transact-SQL (2005)

Parameter

Author

Topic

MBala69
Starting Member

4 Posts

Posted - 2007-11-09 : 10:41:27

Hey,
I am writing a stor proc with a variable in it
eg. declare @empname nvarchar(255)
Select empname from employee
where empname = @empname

This stor proc is going to be used for SRS. When a user picks ALL it should give me an output of all the empname in the employee table or she could type just one name and the out put would be of just one person. My problem is i am not sure how to write in the stor porc for the variable to bring in all the employee name. Is there a way to do it. Could you please let me know.
Thank you,
Mattie

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2007-11-09 : 11:45:31

CREATE PROC MyProc (
@EmpName nvarchar(255)
)
as
if @EmpName = 'ALL'
select EmpName, EmpID, ...
from Employee
else
select EmpName, EmpID, ...
from Employee
where EmpName = @EmpName

Or optionally

select EmpName, EmpID, ...
from Employee
where @EmpName = 'ALL' or EmpName = @EmpName

=======================================
If Tyranny and Oppression come to this land, it will be in the guise of fighting a foreign enemy. -James Madison, fourth US president (1751-1836)

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-09 : 12:33:22

The first one is much better from the perf perspective

MBala69
Starting Member

4 Posts

Posted - 2007-11-09 : 15:15:13

Thank u very much.
Mattie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources