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
About Hardono
