August 28, 2008 at 10:44 am
I have this stored procedure
USE [vdb]
GO
08/28/2008 10:23:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetStreetType]
(
@addrNumber [VarChar] (8),
@addrDirection [VarChar] (2),
@addrStreet [VarChar] (45),
@addrPostDirection [VarChar] (2),
@addrUType [VarChar] (6),
@addrUnit [VarChar] (8),
@addrZipcode [VarChar] (5)
)
AS
BEGIN
SELECT distinct TYPE
FROM dbo.VRSTREET a
WHERE a.NAME = @addrStreet
AND a.ZIPCODE = @addrZipcode
AND (ltrim(rtrim(@addrNumber)) >= a.LO_RANGE AND @addrNumber <= a.HI_RANGE)
AND a.POST_DIR =
(CASE WHEN @addrPostDirection = '' THEN NULL
ELSE @addrPostDirection
END)
AND a.DIRECTION =
(CASE WHEN @addrDirection = '' THEN NULL
ELSE @addrDirection
END)
AND a.UNIT_TYPE=
(CASE WHEN @addrUType = '' THEN NULL
ELSE @addrUType
END)
AND (ltrim(rtrim(@addrUnit)) = a.UNIT_LOW)
END
What i'd like for it to do is replace the empty string values passed in with an is null but for some reason i can't seem to make it work correctly.
Is there a better way to do this?
Thanks
--
August 28, 2008 at 10:59 am
You can replace the CASE statements with the NULLIF() function. You would do:
NullIF(@variable, '')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 11:10 am
Ah Thanks!
So would this be okay?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetStreetType]
(
@addrNumber [VarChar] (8),
@addrDirection [VarChar] (2),
@addrStreet [VarChar] (45),
@addrPostDirection [VarChar] (2),
@addrUType [VarChar] (6),
@addrUnit [VarChar] (8),
@addrZipcode [VarChar] (5)
)
AS
BEGIN
SELECT distinct TYPE
FROM dbo.VRSTRT a
WHERE a.NAME = @addrStreet
AND a.ZIPCODE = @addrZipcode
AND (ltrim(rtrim(@addrNumber)) >= a.LO_RANGE AND @addrNumber <= a.HI_RANGE)
AND a.POST_DIR = NULLIF(@addrPostDirection,'')
AND a.POST_DIR = NULLIF(@addrDirection ,'')
AND a.UNIT_TYPE = NULLIF(@addrUType,'')
AND (ltrim(rtrim(@addrUnit)) = a.UNIT_LOW)
END
August 28, 2008 at 11:21 am
Yes that looks okay.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 11:59 am
Okay so i tried that code and i still couldnt get a match.
Now i also tried a simple select statement and did a comparison between
if column is null and column = null and for = null i couldnt get any results but for is null i found a bunch of results.
I had two question:
1) Is there any difference in the way the two operate?
2) Is there any way i can take the existing code and make it into is null without using huge case statements?
Thanks once again for all the help!
August 28, 2008 at 1:19 pm
I noticed that all of your comparisons are in the format
AND a.POST_DIR =
(CASE WHEN @addrPostDirection = '' THEN NULL
ELSE @addrPostDirection
END)
if you replace it with a null, the comparison will already return false, which means if any of your comparisons have a blank string, then the whole query will be false
I just wanted to make sure you realized this.
P.S. is it faster to have to have the query run with the null comparisons or just check before hand to make sure that all of the string aren't blank, eg. business logic or and if statement before the query runs.
August 28, 2008 at 1:29 pm
Sorry this is my bad. I answered incorrectly in the first post. You can use the NULLIF command, but in SQL by defualt NULL never equals anything including another NULL. If what you are trying to accomplish is to return the row if it matches the parameter or it is NULL then you want the OR condition like this:
(a.POST_DIR = @addrPostDirection OR a.POST_DIR Is Null)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 1:54 pm
Okay that works but what if i were to pass in an invalid POST Direction and in the table it was null, then it would still match wouldnt it?
I don't want to return values that don't match exactly if they are not passed in as empty string.
This is what i came up with but i'm not happy with it.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_GetStreetType]
(
@addrNumber [VarChar] (8),
@addrDirection [VarChar] (2),
@addrStreet [VarChar] (45),
@addrPostDirection [VarChar] (2),
@addrUType [VarChar] (6),
@addrUnit [VarChar] (8),
@addrZipcode [VarChar] (5)
)
AS
BEGIN
SELECT distinct [TYPE]
FROM dbo.VRSTRT a
WHERE a.NAME = @addrStreet
AND a.ZIPCODE = @addrZipcode
AND (ltrim(rtrim(@addrNumber)) >= a.LO_RANGE AND @addrNumber <= a.HI_RANGE)
AND (CASE WHEN @addrDirection = '' THEN '*'
ELSE a.DIRECTION
END) =
(CASE WHEN @addrDirection = '' THEN '*'
ELSE @addrDirection
END)
AND (CASE WHEN @addrPostDirection = '' THEN '*'
ELSE a.POST_DIR
END) =
(CASE WHEN @addrPostDirection = '' THEN '*'
ELSE @addrPostDirection
END)
AND (CASE WHEN @addrUType = '' THEN '*'
ELSE a.UNIT_TYPE
END) =
(CASE WHEN @addrUType = '' THEN '*'
ELSE @addrUType
END)
END
Thanks anyways for all your help!
August 28, 2008 at 1:59 pm
the only time you will get a 'null' result from passing a non-matching join is in with outer joins. If you have something in the where clause and you have a comparison, it you NEVER get a null as a match until your use something like 'isnull (column1) = 1' or something
August 29, 2008 at 3:10 am
Hello,
I read all the posts twice and there seems to be some confusion on both sides :w00t:
Main thing the we need to know is how do you wish to treat the NULLs/empty strings. You posted some example, but you say you are not happy with it, not specifying whether it gives correct results or not...
So, what do you need? From your attempt at solving it seems that the first is correct...
- if parameter is empty, ignore this condition (return rows regardless of what's in the respective column)
- if parameter is empty, return only those rows where the respective column holds NULL
Another thing, what if the value in column is empty string? Can that happen, or is there some rule that enforces that the column contains NULL or nonempty value?
I would write the query like this (based on assumption that if parameter is empty, it should be ignored); it should do generally the same as your last posted query, with additional treatment of NULL value in the parameters (not necessary if parameter can never be NULL):
SELECT distinct [TYPE]
FROM dbo.VRSTRT a
WHERE a.NAME = @addrStreet
AND a.ZIPCODE = @addrZipcode
AND (ltrim(rtrim(@addrNumber)) >= a.LO_RANGE AND @addrNumber <= a.HI_RANGE)
AND (ISNULL(@addrDirection,'') = '' OR @addrDirection=a.DIRECTION)
AND (ISNULL(@addrPostDirection,'') = '' OR @addrPostDirection=a.POST_DIR)
AND (ISNULL(@addrUType,'') = '' OR @addrUType=a.UNIT_TYPE)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply