SQL Dynamic Search Query

Question

Dear all..

 

I have a table as -

 

ID	User	Location	Flag
1	Ashok	Mumbai	1
2	Reema	Mumbai	1
3	Kesar	Haryana	1
4	Hardik	Kolkata	1
5	Rajat	Kolkata	1

 

Front-End -

1. CheckListbox - List of users.

2. CheckListbox - List of Locations.

User can select multiple Users or/and Locations.

For null values (eg - if no Location is selected, System.DBValue.Null is passed)

1. If User - Ashok, then only one record must be displayed.

SQL Stored Procedure -

@User varchar(max),

@Location varchar(max)

 

declare @SQLQuery nvarchar(4000)

 declare @Whereclause nvarchar(4000)

set @SQLQuery = 'select ID, User, Location from UserLocationTable where Flag = 1'

 

if @User is not null

  set @Whereclause =' and User in ('+@User+)'

if @Location is not null

set @Whereclause =' and Location in ('+@Location+)' 

 

set @SQLQuery = @SQLQuery + @Whereclause -

 

Issue - Passing System.DBNull.Value.ToString() from front-end makes the query as -

 

select ID, User, Location from UserLocationTable where Flag =1 and Location in ('')

and User in ('Ashok')

----------

How to solve this ?? Please guide