MSSQL中存储过程的可选参数的定义和使用

编辑日志：

160508创建

160530优化添加：显示传参

160918 添加：SQL高亮显示

可选参数的存在，可以极大的降低代码的重复冗余。在数据库开发中，也是如此。现在针对MSSQL中存储过程的可选参数的定义和使用进行基本的介绍，留作备忘。

#准备工作：

在db_test中建立一张测试表T_test：

USE db_test;

CREATE TABLE dbo.T_test
(
Id  INT  IDENTITY(1,1) NOT NULL
,Name  NVARCHAR(20) NOT NULL
,Sex  BIT  DEFAULT(0)
);

插入一些数据：

[code=SQL]

INSERT INTO dbo.T_test(Name,Sex)
VALUES(N'NAME1','1')
,(N'NAME2','0')
,(N'NAME3','1')
,(N'NAME4','0');

[/code]

查询测试数据：

[code=SQL]
SELECT Id,Name,Sex
FROM dbo.T_test;
[/code]

结果为：

[code=SQL]

Id Name Sex
1 NAME1 1
2 NAME2 0
3 NAME3 1
4 NAME4 0

[/code]

#存储过程

创建一个存储过程：

[code=SQL]
IF OBJECT_ID('dbo.usp_test_get_name_by_id','P') IS NOT NULL
DROP PROC dbo.usp_test_get_name_by_id;
GO

CREATE PROC dbo.usp_test_get_name_by_id
@Name AS NVARCHAR(20) OUTPUT
,@Id AS INT = 1  --DEFAULT VALUE: 1
,@Sex AS BIT = 1 --DEFAULT VALUE: 1

AS
BEGIN 

SET NOCOUNT ON;

SELECT @Name = Name
FROM dbo.T_test
WHERE  Id = @Id
  AND Sex = @Sex;

END
GO
[/code]

对存储过程进行调用：

1）不传递ID和Sex

[code=SQL]
DECLARE @Name NVARCHAR(20);
EXEC dbo.usp_test_get_name_by_id @Name OUTPUT;

SELECT @Name AS NAME;
[/code]

结果为：

[code=SQL]
NAME
-------------
NAME1
[/code]

说明：

可见，当调用存储过程的时候不传递ID，则存储过程会使用(存储过程)定义时设定的ID的默认值1，Sex默认值为1 。

2）传递ID

[code=SQL]
DECLARE @Name NVARCHAR(20);
EXEC dbo.usp_test_get_name_by_id @Name OUTPUT,'3';

SELECT @Name AS NAME;
[/code]

结果为：

[code=SQL]

NAME

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

NAME3

[/code]

说明：

当调用时传递ID，则存储过程在执行的时候会使用从外部传递进来的ID值，因为没有传递进来Sex的值，故使用Sex默认值1进行SQL查询。

3)显式传参【新加160530】

        上面介绍的都属于"非显式"传参，即：所传参数的位置必须严格按照存储过程定义时的位置进行"安排"。这样的限制，对于调用和后期维护而言是非常恶心的事情，这类似于：在SELECT查询中使用   *  将所有符合要求的数据返回给调用者，而所得数据的字段顺序是随着查询时表中字段的顺序来确定的，即：如果后期维护对表中字段的顺序进行了改变，那么将会对调用者带来"连锁反应"，这是不能忽视的。

1>查询Id为3，Sex为1的姓名：

[code=SQL]

DECLARE @Name NVARCHAR(20);
EXEC dbo.usp_test_get_name_by_id @Name OUTPUT,3,1;

SELECT @Name AS NAME;

[/code]

查询结果为：

[code=SQL]
NAME

------------
NAME3
[/code]

2>如果调用存储过程时，手抖了一下，3和1的位置颠倒了，那么查询结果为：

[code=SQL]
NAME

------------
NAME1
[/code]

看！神奇的返回了Id为1的那条数据！虽然这不是我们想要的数据，但这是符合逻辑的。因为MSSQL在匹配参数时，将3转换为了bit类型，因   3 <>  0  ，所以转换成bit后的值为1。你可以尝试换一下其他数。

3>MSSQL中可以进行显示传参：

对于上面2>中的情况，使用显示传参会非常的爽：

[code=SQL]

SELECT * FROM dbo.T_test;

DECLARE @Name NVARCHAR(20);
EXEC dbo.usp_test_get_name_by_id 
@Name = @Name OUTPUT
,@Sex = 1
,@Id = 3;


SELECT @Name AS NAME;

[/code]

查询结果为：

[code=SQL]

NAME

------------
NAME3

[/code]

所以，你可以在调用存储过程时，按照你想要的参数传递顺序进行编辑，而不是必须严格依赖于存储过程定义(或最新定义)中参数的位置。

这样，就算后期存储过程对参数的顺序进行了修改，也不会对调用有任何的影响！