In stored procedures: if a parameter is not made optional, and stored procedure is tried to execute without supplying a parameter, SQL causes an error.
Following is error:
USE Pubs
Go
//Create a Stored Procedure without default value///
CREATE PROCEDURE SP_findaStoreSales
@SPV_storeName AS VARCHAR (50)
SELECT * FROM sales WHERE stor_id=@SPV_storeName;
//Call SP without default value: error///
EXEC findaStoreSales;
//result///
Server: Msg 201, Level 16, State 4, Procedure SP_findaStoreSales, Line 0
Procedure 'SP_findaStoreSales' expects parameter '@SPV_storeName', which was not supplied.
To make a parameter optional, following example demonstrates how to supply a default value:
USE Pubs
Go
//Create a Stored Procedure with default value null///
CREATE PROCEDURE SP_findaStoreSales
@SPV_storeName AS VARCHAR(50) = NULL
AS
IF @SPV_storeName IS NOT NULL
SELECT * FROM sales WHERE stor_id=@SPV_storeName
ELSE
PRINT 'provide a store name'
Results:
Call SP without default value: OK
EXEC SP_findaStoreSales;
provide a store name
Call SP with value: OK
EXEC SP_findaStoreSales '7131';
stor_id ord_num ord_date
------- -------------------- ------------------------------------------
7131 N914008 1994-09-14 00:00:00.000
7131 N914014 1994-09-14 00:00:00.000
7131 P3087a 1993-05-29 00:00:00.000
7131 P3087a 1993-05-29 00:00:00.000
7131 P3087a 1993-05-29 00:00:00.000
7131 P3087a 1993-05-29 00:00:00.000
(6 row(s) affected)
If the procedure is called without default value, IF block is executed. Otherwise ELSE block is executed. In both case, error is avoided.