SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PLS HELP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter7218
Starting Member

2 Posts

Posted - 04/22/2013 :  09:33:25  Show Profile  Reply with Quote
I am puzzled. What is the purpose of this query?

declare @empName varchar(200),
@salary money
set @empName=null
set @salary=1000
select *
from Employee_Test
where (@empName is null or Emp_name=@empName)
and (@salary is null or Emp_Sal=@salary)

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 04/22/2013 :  09:36:31  Show Profile  Reply with Quote
Purpose is:

If you enter any value, then retrieves records which satisfies that condition
Otherwise it will show all records (in the case of @empname = null or @salary = null)

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/22/2013 :  09:41:44  Show Profile  Reply with Quote
Kinda quiz?? :)

Employess without name but salary as 1000

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/22/2013 :  11:55:30  Show Profile  Reply with Quote
quote:
Originally posted by MIK_2008

Kinda quiz?? :)

Employess without name but salary as 1000

Cheers
MIK



Nope

its just retrieving list of employees who have the passed salary value (1000)

the empname = null is to bypass the employeename filter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Peter7218
Starting Member

2 Posts

Posted - 04/22/2013 :  19:21:56  Show Profile  Reply with Quote
Any expert out there, PLEASE help to solve my puzzle.
Go to Top of Page

logpop
Starting Member

16 Posts

Posted - 04/22/2013 :  19:44:57  Show Profile  Reply with Quote
It lists all employees with salary 1000.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/22/2013 :  19:50:57  Show Profile  Reply with Quote
As visakh16 explained, the query returns all records where employee salary is 1000.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 04/22/2013 :  21:16:19  Show Profile  Reply with Quote
Just a word of caution: when you use this technique to bypass search conditions in a stored procedure, that can result in some extremely bad execution plans. See Sommarskog's article here for analysis and alternatives: http://www.sommarskog.se/dyn-search-2008.html

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/23/2013 :  12:41:48  Show Profile  Reply with Quote
quote:
Originally posted by Peter7218

Any expert out there, PLEASE help to solve my puzzle.


whats the puzzle? isnt my statement answer your puzzle?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000