Need to change where clause of Select dynamically

Posted by Somasundaram under Sql Server on 9/15/2012 | Points: 10 | Views : 3209 | Status : [Member] | Replies : 4
Hi.,

This is my Query:

If i know the BusinessUnit ID i have to pass in where clause, Like the Query shown below,

select * from tblHRIMS_EmployeeDetail
where nvrResignedStatus='No' and nvrBusinessUnit= 'BSU-001'

Resultant of this query i query is : 500 Rows

If i dont know the BusinessUnit Id i don't need to pass in where clause, Like the Query shown below,

select * from tblHRIMS_EmployeeDetail
where nvrResignedStatus='No'

Resultant of this query i query is : 1500 Rows

How change the Where clause dynamically, according to input given.

Note: I need in only SQL Query, not by Stored Procedure.

Regards,
V.Somasundaram




Responses

Posted by: Pandians on: 9/15/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!
Declare @nvrResignedStatus NVarchar(10), @nvrBusinessUnit NVarchar(25)

Select @nvrResignedStatus='No', @nvrBusinessUnit='BSU-001'

If (@nvrResignedStatus ='')
Select @nvrResignedStatus = NULL

If (@nvrBusinessUnit ='')
Select @nvrBusinessUnit = NULL

Select * from tblHRIMS_EmployeeDetail
where ((nvrResignedStatus=@nvrResignedStatus Or @nvrResignedStatus Is Null)
And (nvrBusinessUnit=@nvrBusinessUnit Or @nvrBusinessUnit Is Null))
Go


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Somasundaram, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.Saravanan on: 9/15/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Try this...


DECLARE
@SqlQuery NVARCHAR(MAX)
, @nvrResignedStatus NVARCHAR(20)
, @nvrBusinessUnit NVARCHAR(50)
, @WhereClause NVARCHAR(255)

SELECT @nvrResignedStatus = '''NO'''
, @nvrBusinessUnit = '' -- '''BSU-001'''
, @SqlQuery = 'SELECT * FROM tblHRIMS_EmployeeDetail'

IF(@nvrResignedStatus <> '')
SET @WhereClause = 'nvrResignedStatus = '+@nvrResignedStatus

IF(@nvrBusinessUnit <> '' AND @WhereClause <> '')
SET @WhereClause = @WhereClause + ' AND nvrBusinessUnit = '+@nvrBusinessUnit
ELSE IF(@nvrBusinessUnit <> '')
SET @WhereClause = 'nvrBusinessUnit = '+@nvrBusinessUnit

SET @SqlQuery = @SqlQuery+ CASE WHEN @WhereClause <>'' THEN ' WHERE '+@WhereClause ELSE '' END

-- PRINT @SqlQuery
EXECUTE SP_EXECUTESQL @SqlQuery


Know more about SP_EXECUTESQL refer the below links...
http://msdn.microsoft.com/en-us/library/ms188001.aspx
http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/

Thanks,
T.Saravanan

Somasundaram, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/17/2012 [Member] Bronze | Points: 25

Up
0
Down
Declare @nvrResignedStatus NVarchar(5), @nvrBusinessUnit NVarchar(15)

Set @nvrResignedStatus='No', @nvrBusinessUnit='BSU-001'

If (@nvrResignedStatus ='')

Set @nvrResignedStatus = NULL

If (@nvrBusinessUnit ='')

Set @nvrBusinessUnit = NULL


Select * from tblHRIMS_EmployeeDetail

where ((nvrResignedStatus=@nvrResignedStatus Or @nvrResignedStatus Is Null) And (nvrBusinessUnit=@nvrBusinessUnit Or @nvrBusinessUnit Is Null))

Somasundaram, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/17/2012 [Member] Bronze | Points: 25

Up
0
Down
Please mark as answer if it helpful to u..That will helps others who search the same..

Somasundaram, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response