Stored Procedure Optional Parameter

Often, the SQL developer will need to create a stored procedure with an optional parameter. I recently had to do this and could not quite remember the syntax/method. Below is an example of the technique I prefer:

CREATE PROCEDURE YourStoredProcName
(
@ParamNullable = null
)
AS

BEGIN
SET NOCOUNT ON;
SELECT COLUMN1, COLUMN2
FROM TABLE1
WHERE COLUMN1 = COALESCE(@ParamNullable, COLUMN1)

END
GO

How does this work?

• @ParamNullable = null
  This allows the parameter ParamNullableto be null
• WHERE COLUMN1 = COALESCE(@ParamNullable, COLUMN1)
  ‘COALESCE’ replaces the first value passed to it with the second value if the first value is null,
  so if @ParamNullable is passed into our stored procedure as null, the resuling where clause will be WHERE COLUMN1 = COLUMN1

SQL Case Statement – One to Watch for!

I’ve just finished debugging a reporting application that was outputting unexpected values. It turned out the problem was due to a case statement in a SQL query.

The query was as follows:

SELECT case
WHEN MyField BETWEEN 0 AND 100 then 'A'
WHEN MyField  BETWEEN 101 AND 200 then 'B'
WHEN MyField  BETWEEN 201 AND 300 then 'C'
END AS RANGE

This returned more rows than we expected, when I added ‘MyField’ to the query output, it became apparent that emptry string values were being included in the final case rule – So we had a number of additional ‘C’ values. The solution is simple – We needed to exclude any rows where ‘MyField’ was empty:

SELECT case
WHEN MyField BETWEEN 0 AND 100 then 'A'
WHEN MyField  BETWEEN 101 AND 200 then 'B'
WHEN MyField  BETWEEN 201 AND 300 then 'C'
END AS RANGE
WHERE MyField <> ''

You will notice that I do not have an exclusion criteria for MyField being NULL – I added this initially, but it seems these rows were excluded from the output – Because NULL is not in any of the case rules – Although I would also have expected that to be case for the empty string values!

Certainly one to be aware of – If we had not spotted this one statistics on one of the reports I look after would have been wildly out, which of course could have had serious implications when decisions are made based on the report output.