SQL | Parameters and return values exercise | Create a procedure setting null values for parameters



Software ==> SQL  (198 exercises)
Version ==> Any version of SQL Server
Topic ==> Parameters and return values  (11 exercises)
Level ==> Average difficulty
Courses ==> Fast-track SQL  /  Advanced SQL
  1. Go into SQL Server Management Studio;
  3. Execute this script.



Create a stored procedure to list out the category name, event date and category id for each event:

Stored Procedures NULLS

Order your data by the event date.

Incorporate parameters for the following:

Parameter Should equal
@CategoryName All or part of the name of the category you're looking for
@After The earliest event date you're looking for
@CategoryId The number of the category you're looking for

Now try adding default values to the parameters and you'll see that it's difficult: what should the default be for @CategoryID? Instead assign NULL as the default value for all 3 parameters:

(@CategoryName varchar(100) = NULL


,@CategoryID INT = NULL)

This in itself won't work, but NULL is a consistent value across all data types which we can test for. Add an OR to the WHERE clause which tests if each parameter is NULL.  For example:

(@CategoryName IS NULL OR CategoryName LIKE '%' + @CategoryName + '%')

You could now test your stored procedure - here are some numbers it should return:

-- there should be 14 events since 1990 about death

EXEC uspCategoryEvents @CategoryName='death', @After='19900101'

-- category 16 is "Royalty", which has 16 events

EXEC uspCategoryEvents @CategoryId=16

Optionally save this as Dull as a NULL.sql and close it down.

This page has 0 threads Add post