CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sravan Kumar
Sravan Kumar

Reputation: 1457

SQL Query for retrieving data by combining two queries

I am writing two queries in my sql procedure to retrieve data based on the parameter provided from the user in my asp.net application.

IF @Action = 'ID'
  SELECT Name,FirstName,LastName,Employee_ID,Location FROM Salary WHERE  Employee_ID=@Employee_ID
ELSE IF @Action = 'LASTNAME'
  SELECT Name,FirstName,LastName,Employee_ID,Location FROM Salary WHERE LastName  like '%' + @LastName + '%'

Now what I want is to combine the query into single query so that irrespective of whether ID is passed as parameter or lastname is passed i must be able to get the data.

Any help would be greatly appreciated. Thanks in Advance.

Upvotes: 2

Views: 129

Answers (4)

Sravan Kumar
Sravan Kumar

Reputation: 1457

This solved my problem 

SELECT Name, FirstName, LastName, Employee_ID, Location FROM Salary
WHERE Employee_ID = ISNULL(@Employee_ID, Employee_ID) and LastName  LIKE '%' + 
ISNULL(@LastName,LastName) + '%'

Upvotes: 0

Devart
Devart

Reputation: 121902

Try this one -

SELECT 
      Name
    , FirstName
    , LastName
    , Employee_ID
    , Location 
FROM dbo.Salary 
WHERE Employee_ID = ISNULL(@Employee_ID, Employee_ID)
    OR LastName  LIKE '%' + @LastName + '%'

Upvotes: 1

Pirion
Pirion

Reputation: 519

SELECT Name,FirstName,LastName,Employee_ID,Location 
FROM Salary 
WHERE  (@Action = 'ID'       AND Employee_ID  = @Employee_ID)
OR     (@Action = 'LASTNAME' AND LastName  like '%' + @LastName + '%')

Upvotes: 0

vemcaster
vemcaster

Reputation: 361

If all parameters can be used together:

SELECT Name,FirstName,LastName,Employee_ID,Location FROM Salary 
WHERE Employee_ID=@Employee_ID or LastName  like '%' + @LastName + '%';

One parameter - one call:

SELECT Name,FirstName,LastName,Employee_ID,Location FROM Salary 
WHERE (@Employee_ID is not null and Employee_ID=@Employee_ID)
   or (@Employee_ID is null and LastName  like '%' + @LastName + '%')

Upvotes: 2

Related Questions