SQL Dynamic Search Query

Riddhi Valecha

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

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp9y ago
Accepted answer
Hi,

Try like below -

if (@Location<>'')
set @Whereclause =' and Location in ('+@Location+)'
Rafnas T P9y ago
@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

and
if Location is selected pass that locations
else
pass @Location as only DBNull.Value from front end and check
Riddhi Valecha9y ago
Hi all..There are 2 checklistbox in front-end ( aspx page)

1. CheckListBoxUsers
foreach(listitem item in CheckListBoxUsers.Items)
{
if (item.selected == true)
string Users = item.selectedvalue.tostring() + ",";
}
2. CheckListBoxLocation
foreach(listitem item in CheckListBoxLocation .Items)
{
if (item.selected == true)
string Location = item.selectedvalue.tostring() + ",";
}
---
if (Users.Lenght <= 0) {Users = "0";)
If(Location.Lenght <= 0) {Location = "0";)
------------
Issue - When I select only user (say Ashok, Reema), I get Users value as "Ashok,Reema"
and Query That I need to form is -
Select id, user,location from table where user in ('Ashok','reema')
But, here , I also pass Location as "0".
In If-condition - I have checked - if @Location = '''0''' then skip
if @Location <> '''0''' then @Whereclause = @Where + Locatiion in ('''+@Location+''')
Fabio Silva Lima9y ago
@riddhi! What is the expected sql result? I will try to simulate that in my envinroment.
Midhun Tp9y ago
Hi,

Have you tried like below -

if ((replace(@Location,'''','')) <> '')
begin
set @SQLQuery = @SQLQuery + @Whereclause.,,

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

set @SQLQuery = @SQLQuery + @Whereclause.,,
end

If this doesn't work, please provide your c# code
Riddhi Valecha9y ago
Hi all,

Any idea - what value can I pass from front-end ?

I tried passing -"", String.Empty, null - But none worked..

What value to pass from front-end and what to write in if-condition

so that I can get the desired query -

select id, location, user from table where user in ('Ashok','reema')

The query that I am getting is -

select id, location, user from table where user in ('Ashok','reema')
and location in ('')

Also, I tried passing "0" ..

In the SQL Server , I checked -
if @LOcation <> '''0'''

Still, the query is select id, location, user from table where location in ('0') and user in ('reema')
--
Please guide ...its a bit urgent..

Please guide..
Midhun Tp9y ago
Hi,

Please try like below -

IF((replace(@location,'''','')) <> '')
Riddhi Valecha9y ago
Hi Midhun,

I checked by passing String.Empty - but still the query that is formed in same -

select id, location, user from table where user in ('Ashok') and Location in ('')

I am making it a dynamic query -

if @Location <> ''
set @SQLQuery = @SQLQuery + @Whereclause.,,

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

set @SQLQuery = @SQLQuery + @Whereclause.,,

what to check in the if-condition (if @Location <> '')??

Also checked it with - if (COALESCE(@Location,'0')) is not null - but didnt work
Midhun Tp9y ago
Hi,

You should pass an empty string from front end. Like string.Empty

Why are you passing ''?
Riddhi Valecha9y ago
Hello all..

Thanks a ton for these answers ..

I am passing '' value from front-end and len('') in SQL Server is 2.

The query that is formed is - select ID, User, Location from table where location in ('') and user in ('Reema')

Please guide - what value to pass from front-end ??
sudipta sanyal9y ago
Hi
Try THIS

CREATE TABLE #temp (ID INT , [User] NVARCHAR(10), [LOCATION] NVARCHAR(10), Flag INT )
INSERT INTO #temp VALUES (1,'Ashok', 'Mumbai' , 1)
INSERT INTO #temp VALUES (2,'Reema', 'Mumbai' , 1)
INSERT INTO #temp VALUES (3,'Kesar', 'Haryana' , 1)
INSERT INTO #temp VALUES (4,'Hardik', 'Kolkata' , 1)
INSERT INTO #temp VALUES (5,'Rajat', 'Kolkata' , 1)
DECLARE
@User Nvarchar(max),
@Location Nvarchar(max)

SELECT @User = '''Ashok'',''Reema''' --, @Location = '''Kolkata'',''Haryana'''

declare @SQLQuery nvarchar(4000)
declare @Whereclause nvarchar(4000)
set @SQLQuery = 'select ID, [User], Location from #temp where Flag = 1'

if @User is not null AND LEN(Ltrim(@User)) <> 0
set @Whereclause =' and [User] in ('+@User+')'
if @Location is not null AND LEN(Ltrim(@Location)) <> 0
set @Whereclause =' and Location in ('+@Location+')'

set @SQLQuery = @SQLQuery + CASE WHEN ISNULL(@Whereclause,'') = '' THEN '' ELSE @Whereclause END
EXECUTE (@SQLQuery)
DROP TABLE #temp
Manas Mohapatra9y ago
Use Trim(), len() to avoid blank values like below:
if @User is not null AND LEN(TRIM(@User)) <> 0
    set @Whereclause =' and User in ('+@User+)'
if @Location is not null AND LEN(TRIM(@Location)) <> 0
    set @Whereclause =' and Location in ('+@Location+)'
Riddhi Valecha9y ago
Hi..

No .. This is not working ..

I checked - The value of isnull('',0) is not 0.

SQL is not considering '' as Null.
Fabio Silva Lima9y ago
Try to use

IF(LEN(ISNULL(Location, 0)) = 0)