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 and Enjoy

 

Possibly Related Posts

• MS-SQL Stored Procedure for Absolute Beginner, Part 1
• MS SQL version of MySQL’s Group_Concat
• Singapore Bus Guide on Handheld Device
• MySQL Collation for UNION

 

prevent passing null to stored procedure parameters (2), stored procedure prevent null (1), stop return null values from stored procedure (1), sql stored procedure prevent null return value (1), prevent the parameter using null value in stored procedure (1), prevent sql sp from taking null values (1), create stored procedure for null value (1), prevent insering null data using storedprocedure (1), prevent blank or null values in stored procedure (1), passing null value to datetime parameter in store procedure (1), pass datetime null value in stored proc (1), null value start date end date store procedure (1), how to avoid empty values for parameters in stored procedure (1), stored procedure prevent null parameter (1)

Advertise Here