If statements not resolving conditions correctly

Question

If statements not resolving conditions correctly

Kode Krazy

Grasshopper

Points: 23
More actions
June 3, 2008 at 3:06 pm

#183043

I have a SPROC that queries 2 tables Items and Catalog and is supposed to return the rows that meet the conditions specified. However, when I execute it, it never returns any rows. I have tried stepping through it using VS 2008 and check each parameters value in the SPROC as it evaluates them, they have values that should let them into the statement(s) to process the SELECT statement, but it just skips over them as if the conditions were false, even though they are obviously not.
I will post the whole SPROC if necessary, unless one of you can give me some idea of what I might be doing wrong.
TIA,
DM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It's hard to say without knowing a bit more. You'll need to post your code as well as the table DDL, sample data (in the form of inserts) and your expected results. Here's a link that will help you get your sample data ready.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Kode Krazy Grasshopper Points: 23 More actions · Answer 2

Here is the code for my SPROC., it skips over every "IF" statement regardless of the value of the parameter:

ALTER PROCEDURE [dbo].[filtersortlikeDGV]

@SrtBy NVARCHAR(20),

@ItmType NVARCHAR(20) = 'All Items',

@CatName NVARCHAR(20) = 'All Catalogs',

@StyleLike NVARCHAR(50) = NULL,

@DescLike NVARCHAR(50) = NULL

AS

SET NOCOUNT ON

/* Filter on Style */

IF @ItmType = 'All Items' AND @CatName = 'All Catalogs' AND @DescLike = NULL AND @StyleLike IS NOT NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Items.Style LIKE '%' + @StyleLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Description */

IF @StyleLike = NULL AND @DescLike IS NOT NULL AND @ItmType = 'All Items' AND @CatName <> 'All Catalogs'

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Items.Description LIKE '%' + @DescLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Catalog Name with Style */

IF @StyleLike IS NOT NULL AND @DescLike = NULL AND @ItmType = 'All Items' AND @CatName <> 'All Catalogs'

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Style LIKE '%' + @StyleLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Catalog Name with Description */

IF @ItmType = 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike IS NOT NULL AND @StyleLike = NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Description LIKE '%' + @DescLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Catalog Name */

IF @ItmType = 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike = NULL AND @StyleLike = NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName)

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Item Type */

IF @ItmType <> 'All Items' AND @CatName = 'All Catalogs' AND @DescLike = NULL AND @StyleLike = NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Items.Category = @ItmType)

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Item Type AND Catalog Name */

IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike = NULL AND @StyleLike = NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Category = @ItmType)

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Item Type, Catalogs, and Description */

IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike is not NULL AND @StyleLike = NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName)

AND (dbo.Items.Category = @ItmType) AND

(dbo.Items.Description LIKE '%' + @DescLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Item Type, Catalogs and Style */

IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike = NULL AND @StyleLike is not NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Category = @ItmType) AND (dbo.Items.Style LIKE '%' + @StyleLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

/* Filter on Everything */

IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike is not NULL AND @StyleLike is not NULL

BEGIN

SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc

FROM dbo.Catalog INNER JOIN

dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog

WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Category = @ItmType) AND (dbo.Items.Description LIKE '%' + @DescLike + '%') AND (dbo.Items.Style LIKE '%' + @StyleLike + '%')

ORDER BY

CASE @SrtBy

WHEN 'Style' THEN dbo.Items.Style

WHEN 'Description' THEN dbo.Items.Description

WHEN 'Category' THEN dbo.Items.Category

WHEN 'Catalog' THEN dbo.Catalog.Description

END

ASC

END

noeld SSC Guru Points: 96590 More actions · Answer 3

dmontgomery (6/4/2008)
Here is the code for my SPROC., it skips over every "IF" statement regardless of the value of the parameter:
ALTER PROCEDURE [dbo].[filtersortlikeDGV]
@SrtBy NVARCHAR(20),
@ItmType NVARCHAR(20) = 'All Items',
@CatName NVARCHAR(20) = 'All Catalogs',
@StyleLike NVARCHAR(50) = NULL,
@DescLike NVARCHAR(50) = NULL
AS
SET NOCOUNT ON
/* Filter on Style */
IF @ItmType = 'All Items' AND @CatName = 'All Catalogs' AND @DescLike = NULL AND @StyleLike IS NOT NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Items.Style LIKE '%' + @StyleLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Description */
IF @StyleLike = NULL AND @DescLike IS NOT NULL AND @ItmType = 'All Items' AND @CatName <> 'All Catalogs'
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Items.Description LIKE '%' + @DescLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Catalog Name with Style */
IF @StyleLike IS NOT NULL AND @DescLike = NULL AND @ItmType = 'All Items' AND @CatName <> 'All Catalogs'
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Style LIKE '%' + @StyleLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Catalog Name with Description */
IF @ItmType = 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike IS NOT NULL AND @StyleLike = NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Description LIKE '%' + @DescLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Catalog Name */
IF @ItmType = 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike = NULL AND @StyleLike = NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName)
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Item Type */
IF @ItmType <> 'All Items' AND @CatName = 'All Catalogs' AND @DescLike = NULL AND @StyleLike = NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Items.Category = @ItmType)
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Item Type AND Catalog Name */
IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike = NULL AND @StyleLike = NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Category = @ItmType)
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Item Type, Catalogs, and Description */
IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike is not NULL AND @StyleLike = NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName)
AND (dbo.Items.Category = @ItmType) AND
(dbo.Items.Description LIKE '%' + @DescLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Item Type, Catalogs and Style */
IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike = NULL AND @StyleLike is not NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Category = @ItmType) AND (dbo.Items.Style LIKE '%' + @StyleLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END
/* Filter on Everything */
IF @ItmType <> 'All Items' AND @CatName <> 'All Catalogs' AND @DescLike is not NULL AND @StyleLike is not NULL
BEGIN
SELECT TOP (100) PERCENT dbo.Items.Style, dbo.Items.Description, dbo.Items.Category, dbo.Items.Page, dbo.Catalog.Description AS CatDesc
FROM dbo.Catalog INNER JOIN
dbo.Items ON dbo.Catalog.Link = dbo.Items.Catalog
WHERE (dbo.Catalog.Description = @CatName) AND (dbo.Items.Category = @ItmType) AND (dbo.Items.Description LIKE '%' + @DescLike + '%') AND (dbo.Items.Style LIKE '%' + @StyleLike + '%')
ORDER BY
CASE @SrtBy
WHEN 'Style' THEN dbo.Items.Style
WHEN 'Description' THEN dbo.Items.Description
WHEN 'Category' THEN dbo.Items.Category
WHEN 'Catalog' THEN dbo.Catalog.Description
END
ASC
END

you are using "@varname = NULL" all over the place ... change it to "@varname IS NULL" instead!

* Noel

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Bingo. That should do it.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Kode Krazy Grasshopper Points: 23 More actions · Answer 5

Thanks all! I knew it had to be something silly like that.

DM

Harit Gohel Ten Centuries Points: 1254 More actions · Answer 6

Harit Gohel

Ten Centuries

Points: 1254

June 6, 2008 at 1:16 pm

#825677

Second Eye always work in this situation.