指定参数
通过指定过程参数，调用程序可以将值传递给过程的主体。 在执行过程期间，这些值可以用于各种目的。 如果将参数标记为 OUTPUT 参数，则过程参数还可以将值返回给调用程序。一个过程最多可以有 2100 个参数，每个参数都有名称、数据类型和方向。 还可以为参数指定默认值（可选）。
将值传递给参数
-- 常量
use LuckyGitlabStats
go
exec GetMember 'webapp',2
-- 变量 类型和创建时保持一致 set赋值，可以用sql server 自带的参数
declare @groupname nchar(100), @rank nchar(10) --声明变量
set @groupname='webapp'; --变量赋值
set @rank=2
exec GetMember @groupname , @rank --执行存储过程
指定参数名称、类型
创建存储过程、声明参数时，参数名称必须以单个@字符开头，并且在过程范围内必须唯一。
必须使用数据类型定义参数。 参数的数据类型确定了在调用过程时该参数所接受值的类型和范围。
显式命名参数并将相应的值赋给过程调用中的每个参数允许按任意顺序提供参数。 例如，如果过程 my_proc 应有三个参数，分别命名为 @first、 @second和 @third，则可以将传递到该过程的值赋给参数名称，例如：
EXECUTE my_proc @second = 2, @first = 1, @third = 3;
注意
如果以 @parameter =value 格式提供了一个参数值，则必须按此格式提供所有的后续参数。
如果未以 @parameter =value 格式传递参数值，则必须按 CREATE PROCEDURE 语句中所列的参数顺序（从左到右）提供值。
指定参数的默认值
如果在声明参数时指定了默认值，则参数被视为可选的。 在过程调用中不需要为可选参数提供值。
在以下情况下使用参数的默认值：
- 在过程调用中未指定参数值。
- 在过程调用中将 DEFAULT 关键字指定为值。
如果没有合适的值可以指定为参数的默认值，则指定 NULL 为默认值。 如果在未提供参数值的情况下执行过程，最好让过程返回自定义的消息。
存储过程GetMember,添加默认值判断
USE [LuckyGitlabStats]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMember]
-- Add the parameters for the stored procedure here
@Groupname nchar(100) = null,
@Rank nchar(10) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @Groupname IS NULL
begin
PRINT 'ERROR: You must specify the name of your group.'
RETURN
end
-- Insert statements for procedure here
SELECT * from Member
where Groupname=@Groupname and RANK = @Rank
END
-- 执行存储过程
use LuckyGitlabStats
go
--exec GetMember 'webapp' , 2
declare
@rank nchar(10),
@Username nchar(100),
@groupname nchar(100);
set @rank=2
set @groupname='webapp'
EXECUTE GetMember 'webapp',2 ,@Username OUTPUT
select @UserName
--输出 RROR: You must specify the name of your group.
输出参数的方向
参数的方向可以为输入（表明将值传递给过程的主体），也可以为输出（表明过程将值返回给调用程序）。 默认为输入参数。
若要指定输出参数，必须在 CREATE PROCEDURE 语句的参数定义中指定 OUTPUT 关键字。 当过程退出时，它向调用程序返回输出参数的当前值。 执行过程时，调用程序也必须使用 OUTPUT 关键字，才能将该参数值保存到可以在调用程序中使用的变量中。
给修改上方例子添加一个输出参数 @Username
USE [LuckyGitlabStats]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMember]
-- Add the parameters for the stored procedure here
@Groupname nchar(100) = null,
@Rank nchar(10) output ,
@Username nchar(100) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @Groupname IS NULL
begin
PRINT 'ERROR: You must specify the last name of the sales person.'
RETURN
end
-- Insert statements for procedure here
SELECT * from Member
where Groupname=@Groupname and RANK=@Rank
select @Username = username
from Member
where Groupname=@Groupname and RANK=@Rank
order by UserName desc
END
-- 执行存储过程
go
--exec GetMember 'webapp' , 2
declare -- @rank nchar(10),
@Username nchar(100);
-- @groupname nchar(100);
-- set @rank=2
-- set @groupname='webapp'
EXECUTE GetMember 'webapp',2 ,@Username OUTPUT
select @UserName
注意
经测试，一个输出参数，只能返回一个值，当返回一个结果集时，会**按照字典序显示首元素**
C# 获取SQL Server print 结果
static public void ExecuteStoredProc()
{
var connectionString = @"sql";
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("GetMember", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@rank", "2");
connection.Open();
// wire up an event handler to the connection.InfoMessage event
connection.InfoMessage += connection_InfoMessage;
var result = command.ExecuteNonQuery();
connection.Close();
}
}
static void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// this gets the print statements (maybe the error statements?)
var outputFromStoredProcedure = e.Message;
Console.WriteLine(e.Message);
}
参考
https://docs.microsoft.com/zh-cn/sql/relational-databases/stored-procedures/specify-parameters
《sql的查询艺术》