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

I was just browsing through MSDN website when I noticed something that all this time I considered it very trivial. Apparently in VB, you need to give every line apostrophe (‘) to make that line a comment (I know this). And you can’t use the underscore (_) to continue the comment, you still need to give the apostrophe again in the next line (now that is new).

Very contrast compared to other language such as C# (or other similar language in that matter, like C/C++, Java), you can use double slashes (//) to start a comment. To make multiple-line comment, you need to start it with /* and end it */

The comparison becomes a moot point if you are using Visual Studio. You can easily select the lines, and press Ctrl+K+C to comment it, and Ctrl+K+U to uncomment the selected lines.