14
Answers

SQL Dynamic Search Query

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 
 
 
 
 

Answers (14)