Consider you have a stored procedure that perform a SELECT operation for an object which is characterized by the ‘Start Date’ and ‘End Date’. Let’s assume we have the following TABLE:

Table Name: Events

Columns:

EventID int

EventName Varchar(64)

EventDesc Varchar(512)

EventStartDate DateTime

EventEndDate DateTime

And let’s assume that we have the following stored procedure:

CREATE PROCEDURE [dbo].[ListFilteredEvents]
	-- Add the parameters for the stored procedure here
	@StartDate datetime,
	@EndDate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	SELECT
		Events.EventID,
		Events.EventName,
		Events.EventDesc,
		Events.EventStartDate,
		Events.EventEndDate
	FROM 
		Events
	WHERE 
		Events.EventStartDate>= @StartDate AND
                Events.EventEndDate<= @EndDate
	ORDER BY 
		Events.EventStartDate
END

Now what will happen if somehow the SP caller will pass blank value as the StartDate and/or EndDate? Error (Exception will be thrown) of course. If we have access to the source code of the application that execute this stored procedure, we could easily error-prevention at the application level. But if our access is only limited to database level, we could do the following:

CREATE PROCEDURE [dbo].[ListFilteredEvents]
	-- Add the parameters for the stored procedure here
	@StartDate datetime,
	@EndDate datetime
AS
BEGIN
   IF @StartDate IS NULL
   BEGIN
      -- Set it with the smallest value of DateTime data type
      SET @StartDate=CAST('1753-01-01' as DATETIME) 
   END

   IF @EndDate IS NULL
   BEGIN
      -- Set it with the biggest value of DateTime data type
      SET @EndDate=CAST('9999-12-31' as DATETIME)
   END

   BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	SELECT
		Events.EventID,
		Events.EventName,
		Events.EventDesc,
		Events.EventStartDate,
		Events.EventEndDate
	FROM 
		Events
	WHERE 
		Events.EventStartDate>= @StartDate AND
                Events.EventEndDate<= @EndDate
	ORDER BY 
		Events.EventStartDate
   END
END

This way we could prevent Error/Exception being thrown whenever a blank value is passed into the stored procedure.

Important Update: Saturday, 21 April 2007 13.43 PM -- Thanks for Bro MCA for pointing out a better solution =)

CREATE PROCEDURE [dbo].[ListFilteredEvents]
	-- Add the parameters for the stored procedure here
	-- Set it with the smallest value of DateTime data type
        @StartDate DateTime='1753-01-01',
	-- Set it with the biggest value of DateTime data type
        @EndDate DateTime='9999-12-31' 
AS 
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Insert statements for procedure here
   SELECT
	Events.EventID,
	Events.EventName,
	Events.EventDesc,
	Events.EventStartDate,
	Events.EventEndDate
   FROM 
	Events
   WHERE 
	Events.EventStartDate>= @StartDate AND
        Events.EventEndDate<= @EndDate
    ORDER BY 
    Events.EventStartDate
END

1. 

   Reply

   mca said: 2007.04.21 09:32

   Hi, put default value after parameter declaration in stored produre. It’s better, cleaner code, and no need to do checking on null with IF .. ELSE branching. Then you stored proc will be faster.

   Or if your database is SQL2005, use Try … Catch block for more elegant exception handling.
2. 

   Reply

   Hardono Arifanto said: 2007.04.21 13:51

   Thanks Bang =) Updated the Article.
3. 

   Reply

   Hardono Arifanto said: 2007.04.21 18:26

   Bang, apparently if I don’t do the checking, the value will still be NULL.

   It seems that the

   SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & “Stored_Procedure_Name”, GetNull(StartDate), GetNull(EndDate))

   will declare and set @StartDate and @EndDate into NULL thus skipped the default value assignment.