SQL Tutorial - Stored Procedures Optional Parameters

Опубликовано: 09 Август 2022
на канале: BeardedDev

2,698

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';