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
loading...
About Hardono
Incoming Search
bugs, sql
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.
Thanks Bang =) Updated the Article.
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.