Stored procedure problem

Question

Post reply

Stored procedure problem

Christopher Martin

Newbie

Points: 8
More actions
December 15, 2005 at 10:07 am

#188676

A quick question for all the experts here.
I have a simple stored procedure that takes two parameters. I wanted to predefine them to null so if the values don't get passed in it grabs all the records, but if the values are there its selects only the date range. It's fairly simple and it was working without a hitch for a long time. Recently it stopped working, and I am not entirely sure why.
A few print statements show me that the conditional is not being met, even if the values are passed into the stored procedure. I have been puzzling over this for a bit and i really cannot figure out what has changed to make this stop working. Does anyone else have any ideas?
Thanks in advance, this is really puzzling me.
I have reproduced the stored procedure, with some sample output, below:
----Stored Proc----
CREATE PROCEDURE usp_page_stats ( @start_date smalldatetime = NULL, @end_date smalldatetime = NULL )
AS
IF @start_date != NULL AND @end_date != NULL
BEGIN
PRINT 'Selecting for specified date range'
SELECT DISTINCT LoggedPAge, COUNT(LoggedPage) AS hit_count, COUNT(DISTINCT ClientIP) as num_visitors
FROM hits WHERE LoggedDate BETWEEN @start_date AND @end_date
GROUP BY LoggedPage
ORDER BY hit_count DESC
END
ELSE
BEGIN
PRINT 'Selecting for all dates'
SELECT DISTINCT LoggedPage, COUNT(LoggedPage) AS hit_count, COUNT(DISTINCT ClientIP) AS num_visitors
FROM hits
GROUP BY LoggedPage
ORDER BY hit_count DESC
END
GO
----End Stored Proc----
----Sample Output NO Parameters----
exec usp_page_stats
Selecting for all dates
(42 row(s) affected)
----End Sample Output----
----Sample Output WITH Parameters----
exec usp_page_stats @start_date='12/14/2005', @end_date='12/14/2005'
Selecting for all dates
(42 row(s) affected)
----End Sample Output----

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

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

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 1

Don't use !=, it can cause issues. Use IS NULL or IS NOT NULL

Christopher Martin Newbie Points: 8 More actions · Answer 2

Wow, thanks!

This seems to have helped, the stored procedure is running the way it's supposed to.

Thanks for the quick response and the help.

I do have a further question though. Could anyone explain 1) what sort of problems using != would cause and why they cause those problems and 2) why this worked at first then stopped?

Again, thanks a LOT for the help.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

NULL is definitely a special case in SQL Server and cannot be treated as just another value (because it isn't). I bet lots of people on here have been caught out writing WHERE clauses like this:

SELECT ... WHERE field <> 'x'

only to find out that they are not getting the records returned that they expected. The solution?

SELECT ... WHERE (field <> 'x') or (field is Null)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 4

There are a couple articles on NULL here and you can get some strange behavior depending on ANSI NULLs setting. Sometimes it isn't equal to itself

David Korb SSCrazy Points: 2505 More actions · Answer 5

I find the ISNULL() function solves most issues for me:

WHERE ISNULL( field, 'x' ) <> 'x'