SQL Tutorial - Stored Procedures Optional Parameters

Learn how to create stored procedures with optional parameters in under 10 minutes. In this video I walk through a simple stored procedure example where I assign default values for parameters within the stored procedure definition, this prevents the need of adding all parameters every time you execute the stored procedure. Assigning default values within the stored procedure definition effectively makes parameters optional. Optional parameters are not currently supported in Azure Synapse Analytics. 00:00 Introduction 00:37 Create Stored Procedure 02:10 Adding additional parameters to stored procedure 03:12 Passing NULLs into stored procedure execution 05:10 Adding optional parameters If you wish to follow along please use the code below: /* create table */ IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL DROP TABLE dbo.Customers; CREATE TABLE dbo.Customers ( CustomerId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers_CustomerId PRIMARY KEY (CustomerId), FirstName VARCHAR(50) NOT NULL, MiddleNames VARCHAR(50) NULL, LastName VARCHAR(50) NOT NULL, DOB DATE NOT NULL, HomeAddressLine1 VARCHAR(50) NOT NULL, HomeAddressLine2 VARCHAR(50) NULL, HomeAddressCity VARCHAR(50) NOT NULL, HomeAddressPostcode VARCHAR(10) NOT NULL, CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_Customers_CreatedDate DEFAULT (CURRENT_TIMESTAMP), ModifiedDate DATETIME2 NOT NULL CONSTRAINT DF_Customers_ModifiedDate DEFAULT (CURRENT_TIMESTAMP) ); /* create stored procedure start */ CREATE OR ALTER PROCEDURE dbo.spInsertCustomers ( @FirstName VARCHAR(50), @LastName VARCHAR(50), @DOB DATE, @HomeAddressLine1 VARCHAR(50), @HomeAddressCity VARCHAR(50), @HomeAddressPostcode VARCHAR(10) ) AS INSERT INTO dbo.Customers (FirstName, LastName, DOB, HomeAddressLine1, HomeAddressCity, HomeAddressPostcode) SELECT @FirstName AS FirstName, @LastName AS LastName, @DOB AS DOB, @HomeAddressLine1 AS HomeAddressLine1, @HomeAddressCity AS HomeAddressCity, @HomeAddressPostcode AS HomeAddressPostcode; /* execute stored procedure */ EXEC dbo.spInsertCustomers @FirstName = 'Domenic', @LastName = 'Durrant', @DOB = '19790620', @HomeAddressLine1 = '1 The Glens', @HomeAddressCity = 'Birmingham', @HomeAddressPostcode = 'B1 6AZ';