How to write this query when args could be null

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)

How to write this query when args could be null

Author

Topic

sspeedy00
Starting Member

2 Posts

Posted - 2009-01-22 : 08:31:44

Hi Everyone,

I have to modify the query I wrote. It takes in three args, @loc, @lastName, @group. Here it is:


SELECT			b.last_name+','+b.first_name as name, *
FROM			hr.dbo.tbljob a
LEFT OUTER JOIN	        hr.dbo.tblemployee b
ON			a.emplid = b.emplid
WHERE			a.empl_status = 'A'
AND               
(                
	        a.work_location = @loc
        AND	b.last_name LIKE @loc + '%'
	AND	a.group = @group)

ORDER BY          b.last_name, b.first_name

Problem is, the three args might not always come in. Perhaps the user wants to perform a search by @loc (location) only.... or maybe @name only, or maybe any 2 of the args or all 3.

How do I look out for something like that in SQL? Keep in mind that this is a stored procedure. TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 08:33:53

[code]SELECT b.last_name + ',' + b.first_name as name,
*
FROM hr.dbo.tbljob a
LEFT JOIN hr.dbo.tblemployee AS b ON a.emplid = b.emplid
WHERE a.empl_status = 'A'
AND (a.work_location = @loc OR @loc IS NULL)
AND (b.last_name LIKE @name + '%' OR @Name IS NULL)
AND (a.group = @group OR @Group IS NULL)
ORDER BY b.last_name,
b.first_name[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 09:29:18

also see this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources