SODEVE

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

Share This

Hardono Arifanto :: Apr.20.2007 :: Database :: 3 Comments »