Definition and use of optional parameters for stored procedures in MSSQL

The existence of optional parameters can greatly reduce the repetitive redundancy of the code. This is true in database development as well. A basic introduction to the definition and use of optional parameters for stored procedures in MSSQL is now available as a memo.

#准备工作:

Create a test table in Db_test t_test:

Use Db_test;

CREATE TABLE dbo. T_test
(
Id INT IDENTITY (+) not NULL
, Name NVARCHAR () not NULL
, Sex BIT DEFAULT (0)
);

Insert some data:

INSERT into dbo. T_test (Name,sex) VALUES (n ' NAME1 ', ' 1 '), (n ' NAME2 ', ' 0 '), (n ' NAME3 ', ' 1 '), (n ' NAME4 ', ' 0 ');

Query test data:

SELECT id,name,sexfrom dbo. T_test;

The result is:

Id Name Sex

-------------------------------

1 NAME11
2 NAME20
3 NAME31
4 NAME40

#存储过程

Create a stored procedure:

IF object_id (' dbo.up_test_get_name_by_id ', ' P ') is not nulldrop PROC dbo.up_test_get_name_by_id; Gocreate PROC Dbo.up_test_get_name_by_id@name as NVARCHAR out, @Id as INT = 1--default value:1, @Sex as BIT = 1--def Ault Value:1asbegin

SELECT @Name = Namefrom dbo. T_testwhere Id = @Id and  Sex = @Sex; Endgo

To make a call to a stored procedure:

1) do not pass ID and sex

DECLARE @Name NVARCHAR (20); EXEC dbo.up_test_get_name_by_id @Name out; SELECT @Name as Name;

The result is:

NAME

-------------

NAME1

Description

It can be seen that when a stored procedure is called without passing an ID, the stored procedure uses the default value of the ID set by the (stored procedure) definition 1,sex The default value of 1.

2) Pass ID

DECLARE @Name NVARCHAR (20); EXEC up_test_get_name_by_id @Name out, ' 3 '; SELECT @Name as Name;

The result is:

NAME

-------------

NAME3

Description

When the call passes the ID, the stored procedure uses the ID value passed in from the outside when it executes, because it does not pass in the value of sex, so it uses the sex default value of 1 for SQL queries.

Definition and use of optional parameters for stored procedures in MSSQL