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