Need to change where clause of Select dynamically

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

0	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

Latest Posts