CODEWITHSUNDEEP
sqlsql-server-2008t-sql
codebased
codebased

Reputation: 7073

Merge two select statements into one; I could not figure out how to do... looks very easy though

Here is the interesting challenge.

I have got one sp with select statement like this:

select * from employee

What I want is user can call this stored procedure with or without employee type parameter. Let say for an example employee type allows 'Internal', 'External' value only. It is an optional parameter.

We want to call this SP and sp should executed with only one select statement

so I want: 

select * from employee 

or 

select * from employee where employeetype in ('Internal')

I want to combine this into one.

I don't want to use If Statement.

If the EmployeeType is not provided the it should return entire employee list.

Please note that I can have more than one employee type in the variable so it has to be like

@employeeType = 'Internal,External,Officer' select * from Employee where Employee in ( @employeeType)

-- run this when EmployeeType is null

or EmployeeType = EmployeeType

Upvotes: 0

Views: 37

Answers (3)

TechDo
TechDo

Reputation: 18629

You can use a parameter to attain the result. Add an input parameter which accepts values 'Internal', 'External' or empty. If empty is the case full data should be returned. For this you can use a case statement. See the sample below:

select * 
from 
    employee 
where 
    employeetype =case when isnull(@var, '')='' then employeetype else @var end

Note: variable @var accepts values 'Internal' or 'External'. To get full data, assign empty. If variable @var comes as null, then below query can be used.

select * 
from 
    employee 
where 
    employeetype =isnull(@var, employeetype)

For coma separated values, try:

select * 
from 
    employee 
where 
    employeetype =(select col from fnSplit(case when isnull(@var,'')='' 
                                            then employeetype else @var end, ','))

Upvotes: 2

highwingers
highwingers

Reputation: 1669

Lets say you have a SP taking one parameter @EType

then send your parameter to SP either with a value or NULL, and modify your SP like this..

Select * From Employees
Where (EmployeeType = @eType OR @eType is NULL)

Upvotes: 1

Z .
Z .

Reputation: 12837

where @EmployeeType is null or EmployeeType = @EmployeeType

Upvotes: 0

Related Questions