Ignore part of where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Ignore part of where clause

Author

Topic

bobred
Starting Member

14 Posts

Posted - 2011-09-06 : 10:44:03

What I am after is a select statement that ignores part of the where condition if either ProductCode or ProductDesc are null, I ccam up with this but returns nothing

SELECT * FROM [Consumables]
WHERE (([ProductCode] LIKE '%' + isnull(@prodcode,'') + '%')
or
([ProductDesc] LIKE '%' + isnull(@proddesc,'') + '%'))

I am using it as part of a ASP.NET program, any Ideas?
Thanks, James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:12:08

[code]SELECT * FROM [Consumables]
WHERE ([ProductCode] LIKE '%' + @prodcode + '%' OR @prodcode IS NULL)
AND ([ProductDesc] LIKE '%' + @proddesc + '%' OR @proddesc IS NULL)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bobred
Starting Member

14 Posts

Posted - 2011-09-06 : 11:20:58

HI

Thanks for the reply, I have just tried this and still doesn't work.
James

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-06 : 11:41:33

SELECT *


FROM [Consumables]
WHERE
    [ProductCode] IS NULL
    OR [ProductDesc] IS NULL
    OR [ProductCode] LIKE '%' + isnull(@prodcode,'') + '%'
    OR [ProductDesc] LIKE '%' + isnull(@proddesc,'') + '%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:47:28

quote:
Originally posted by bobred

HI

Thanks for the reply, I have just tried this and still doesn't work.
James

show some sample data and output you want out of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-06 : 12:47:05

"OR [ProductDesc] LIKE '%' + isnull(@proddesc,'') + '%'"

Note that this won't match ProductDesc = NULL.

TC has taken care of that with "OR [ProductDesc] IS NULL" - but that will mean you always get a [Consumables] if its [ProductDesc] = NULL regardless of any other parameters

I personally favour Visakh's:


SELECT * FROM [Consumables] 
WHERE ([ProductCode] LIKE '%' + @prodcode + '%' OR @prodcode IS NULL)
AND ([ProductDesc] LIKE '%' + @proddesc + '%' OR @proddesc IS NULL)

when you want @Parameters that must either match, or themselves be NULL, so it would help to know why this isn't working for you

bobred
Starting Member

14 Posts

Posted - 2011-09-06 : 15:25:28

Hi

I have tried

SELECT * FROM [Consumables]
WHERE ([ProductCode] LIKE '%' + @prodcode + '%' OR @prodcode IS NULL)
AND ([ProductDesc] LIKE '%' + @proddesc + '%' OR @proddesc IS NULL)

Which works in MS SQL Management Studio but not in the ASP.NET application, it only gives information when both variables are not null.
James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 22:52:07

why not in asp.net? how are you calling this query in asp.net?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 03:02:21

"but not in the ASP.NET application"

Is your ASP.NET application passing the variable as a blank string, rather than NULL, perhaps?

bobred
Starting Member

14 Posts

Posted - 2011-09-07 : 03:53:07

Here is the ASP code


<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
            SelectCommand="SELECT * FROM [Consumables]
WHERE ([ProductCode] LIKE '%' + @prodcode + '%' OR @prodcode IS NULL)
AND ([ProductDesc] LIKE '%' + @proddesc + '%' OR @proddesc IS NULL)">
            <SelectParameters>
                <asp:ControlParameter ControlID="productsearch" Name="prodcode" 
                    PropertyName="Text" Type="String" />

I think you may be on to something Kristen, I shall investigate.
James
                <asp:ControlParameter ControlID="descsearch" Name="proddesc" 
                    PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-07 : 04:16:28

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

bobred
Starting Member

14 Posts

Posted - 2011-09-07 : 04:25:33

Hi

All sorted, it was that I was passing an empty string. Here is the working code.


<asp:SqlDataSource CancelSelectOnNullParameter="false"  ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
            SelectCommand="SELECT * FROM [Consumables]
WHERE ([ProductCode] LIKE '%' + @prodcode + '%' OR @prodcode IS NULL)
AND ([ProductDesc] LIKE '%' + @proddesc + '%' OR @proddesc IS NULL)">
            <SelectParameters>
                <asp:ControlParameter ControlID="productsearch" Name="prodcode" 
                    PropertyName="Text" Type="String" ConvertEmptyStringToNull="true" />
                <asp:ControlParameter ControlID="descsearch" Name="proddesc" 
                    PropertyName="Text" Type="String" ConvertEmptyStringToNull="true" />
            </SelectParameters>
        </asp:SqlDataSource>

I added ConvertEmptyStringToNull="true" and CancelSelectOnNullParameter="false" which worked.

Thanks for all the help.
James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 04:30:19

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-07 : 04:48:08

Go read my blog post please. Your solution works, but it's a performance nightmare waiting to happen.

--
Gail Shaw
SQL Server MVP

bobred
Starting Member

14 Posts

Posted - 2011-09-07 : 05:17:18

Hi Gail, could you direct me to where I should look.
Thanks, James

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 05:25:26

You would be better with sp_ExecuteSQL for a variable number of parameters like that. I reckon that will be explained in Gail's article ...

Then you wouldn't need to fuss with Empty/Null checks - you could exclude them from the WHERE clause "string" when "blank", but still include the parameters for sp_ExecuteSQL as it doesn't mind having more parameters than are actually used.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-07 : 08:29:48

The blog post I referenced earlier in this thread:

quote:
Originally posted by GilaMonster

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-07 : 20:43:40

quote:

What I am after is a select statement that ignores part of the where condition if either ProductCode or ProductDesc are null, I ccam up with this but returns nothing

Thought you meant when either of the *columns* were null.

Follow gail's advice. I usually repost that link when I see this kind of question

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources