Check for valid paramete and null detection

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 2005 Forums

Transact-SQL (2005)

Check for valid paramete and null detection

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2015-03-22 : 23:12:29

This should be rather simple. To begin this Stored procedure I need to check 2 incoming parameters. If @mostRecentDate is not given ( assuming its NULL, I will then use GETDATE() to use today. However, if the @oldestWhenDate is null, I wont process the request. I have added print statements but I still cannot see what's wrong. Must be simple.

Here's the SP section

ALTER PROCEDURE [dbo].[usp_PROCESS_DateRange]

@oldestWhenDate smalldatetime = null,
@mostRecentDate smalldatetime=null

AS
BEGIN

declare @LNStartDate smalldatetime
declare @LNEndDate smalldatetime
PRINT 'FIRST CHECK @mostRecentDate for NULL'
PRINT ' @mostRecentDate '+ coalesce(Convert(varchar(30), @mostRecentDate ),'@mostRecentDate is null')

IF ( @mostRecentDate=null)
BEGIN
PRINT ' no end date. so GETDATE'
set @mostRecentDate=GetDate()
END
SET @LNEndDate=@mostRecentDate
PRINT 'assign end date is: ' + coalesce(Convert(varchar(15), @LNEndDate),' @LNEndDate is null')

PRINT '2nd CHECK @oldestWhenDate NOT NULL'
IF ( @oldestWhenDate !=null)
BEGIN
PRINT ' assign variable for start date'
SET @LNStartDate= @oldestWhenDate
END

IF ( @LNStartDate=null)
BEGIN
PRINT ' error, no begin date given must exit'
return
END

END

RETURN

When I use the SSMS and bypass the option to do input parameters, I get this:
FIRST CHECK @mostRecentDate for NULL
@mostRecentDate @mostRecentDate is null
assign end date is: @LNEndDate is null
2nd CHECK @oldestWhenDate NOT NULL

I expected GETDATE to have been used.
And if the values are not NULL, I'd like to know what they are.
How do I use IF statements for these values?

Thanks

andrewcw

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 08:58:00

To check for nulls, write


IF ( @mostRecentDate is null)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources