August 10, 2005 at 7:56 am
I am writing a stored procedure which need to compare two dates. I have a function which looks up a date key against a date table, and this is used to return the dates needed. If the nmonths differ, I need to return true. However, my code soesn’t seem to set the return code at all. Cn anyone see the problem (maybe I can’t use a function in this context?)
ALTER PROCEDURE usp_CheckforPartitionRequirement
@NewLatestLoadNo INT,
@NewPartitionFlag BIT OUTPUT
AS
DECLARE @PartitionLatestLoad DATETIME
DECLARE @LatestDateforPartitionsKey INT
SET @LatestDateforPartitionsKey = (select MAX(LatestLoadDateSkey) from PartitionLoadLog
where PartitionName = 'MyPartition')
SET @PartitionLatestLoad =(SELECT dbo.fn_LookupDate(@LatestDateforPartitionsKey))
IF DATEPART(month,(SELECT dbo.fn_LookupDate(@NewLatestLoadNo))) > DATEPART (month,@PartitionLatestLoad)
SET @NewPartitionFlag = 1
ELSE
SET @NewPartitionFlag = 0
This is my function, which works on it’s own
CREATE FUNCTION fn_LookupDate (@dateKey INT)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Date DATETIME
SET @date =
(SELECT Date from [EL1.3]..DateDimension
WHERE dateskey = @dateKey)
RETURN(@date)
END;
GO
August 10, 2005 at 8:13 am
Should you be testing for a returned NULL from the function?
August 10, 2005 at 8:18 am
Have you run the stored proc with the debugger? It might shed some light on things.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 10, 2005 at 8:37 am
Strictly for what it's worth: I program in the "paranoid" style. That is, trust no inputs, trust no database contents, trust no one, trust nothing. As a result, my procedures tend to be longer than those written by others. I rationalize this to myself by not needing to spend a lot of time debugging -- I'd rather write too much code than spend too much time debugging.
Here's how your procedure might look if coded in the "paranoid" style (I haven't compiled or tested it)...
ALTER PROCEDURE usp_CheckforPartitionRequirement
@NewLatestLoadNo INT
, @NewPartitionFlag BIT OUTPUT
AS
/*
||====================================================================
|| Local variable declarations...
||====================================================================
*/
DECLARE @PartitionLatestLoad DATETIME
, @NewLatestLoadDate DATETIME
, @LatestDateforPartitionsKey INT
, @retc INT
, @msg VARCHAR (255)
/*
||====================================================================
|| Initializations...
||====================================================================
*/
SELECT @retc = 0
, @msg = ''
/*
||====================================================================
|| Validate input parameter...
||====================================================================
*/
IF @NewLatestLoadNo IS NULL
--THEN
BEGIN
SELECT @msg = ' please pass in a non-NULL @NewLatestLoadNo parameter'
GOTO PRC_ERROR
END
--END IF
/*
||====================================================================
|| Pull "latest date" partitions keys, testing for validity...
||====================================================================
*/
SELECT @LatestDateforPartitionsKey = MAX (LatestLoadDateSkey)
FROM PartitionLoadLog
WHERE PartitionName = 'MyPartition'
IF @LatestDateforPartitionsKey IS NULL
--THEN
BEGIN
SELECT @msg = ' "SELECT MAX (LatestLoadDateSkey) FROM PartitionLoadLog" returned NULL'
GOTO PRC_ERROR
END
--END IF
/*
||====================================================================
|| Pull "latest load date" for the partitions key, testing for
|| validity...
||====================================================================
*/
SELECT @PartitionLatestLoad = dbo.fn_LookupDate (@LatestDateforPartitionsKey)
IF @PartitionLatestLoad IS NULL
--THEN
BEGIN
SELECT @msg = ' dbo.fn_LookupDate (@PartitionLatestLoad) function call returned NULL'
GOTO PRC_ERROR
END
--END IF
/*
||====================================================================
|| Pull "new latest load date" for the partitions key based on input
|| parameter, testing for validity...
||====================================================================
*/
SELECT @NewLatestLoadDate = dbo.fn_LookupDate (@NewLatestLoadNo)
IF @NewLatestLoadDate IS NULL
--THEN
BEGIN
SELECT @msg = ' dbo.fn_LookupDate (@NewLatestLoadDate) function call returned NULL'
GOTO PRC_ERROR
END
--END IF
/*
||====================================================================
|| We've only gotten this far if every operation and function call has
|| gotten us a non-NULL value...
||====================================================================
*/
IF DATEPART (MONTH, @NewLatestLoadDate) > DATEPART (MONTH, @PartitionLatestLoad)
--THEN
SET @NewPartitionFlag = 1
ELSE
SET @NewPartitionFlag = 0
--END IF
/*
||====================================================================
|| If no errors detected, skip the error trap...
||====================================================================
*/
GOTO PRC_EXIT
PRC_ERROR:
/*
||====================================================================
|| Error trap...
||====================================================================
*/
SELECT @retc = -1
, @msg = '????? error in procedure ' + OBJECT_NAME (@@PROCID)
+ ': ' + @msg
RAISERROR (@msg, 16, 1)
PRC_EXIT:
/*
||====================================================================
|| Exit...
||====================================================================
*/
RETURN @retc
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply