找出存储过程参数的默认值

引言

当您将使用 ADO.NET 的 .NET 应用程序迁移到使用 JDBC 的基于 Java 的应用程序时，通常需要添加代码以将所有存储过程参数添加到 SqlCommand 对象中，因为 SQL JDBC 不允许您“忽略”具有默认值的参数。 在本文中，您将了解如何使用一些 T-SQL 和 C# 代码从存储过程中派生所有参数，以便您可以调用存储过程并自动将默认参数分配给它们的默认值。

由于 SQL Server 不会将存储过程参数的默认值存储在其系统表中，而是在运行时评估过程的文本，因此在编写使用 T-SQL 存储过程的应用程序时会失去一些灵活性。 可以使用一些 C# 和一些 T-SQL 解决此问题。 首先使用名为 _GetAllProcedures 的 T-SQL 存储过程

CREATE PROCEDURE _GetAllProcedures 
AS 
SET NOCOUNT ON 
select sysobjects.name,syscolumns.name from sysobjects, syscolumns 
where 
sysobjects.xtype='P' and 
sysobjects.id = syscolumns.id 
RETURN 
GO

将使用此存储过程来获取所有存储过程并评估其参数的 C# 代码非常简单。 首先，使用与数据库的两个连接——我将它们称为“主”连接和“参数”连接

SqlConnection conSP = new SqlConnection(ConnectionString); 
SqlConnection conSPParamValue = 
    new SqlConnection(ConnectionString); 
conSP.Open(); 
conSPParamValue.Open();

接下来，在主连接上设置一个 SqlCommand 并使用您感兴趣的存储过程初始化它

SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP); 
cmdSP.CommandType = CommandType.StoredProcedure;

接下来，代码将循环遍历此存储过程的结果，并忽略所有以“dt_”前缀开头的系统存储过程。 花絮 – 'dt' 代表 'DaVinci Tools'，这是使用 Enteprise Manager 或设计工具自动生成代码时使用的技术的早期代码名称，因此您可以说您真正找到了“达芬奇密码”。

当该过程不是“dt_”过程时，您然后在“params”连接上创建一个新命令到“_GetParamDefault”存储过程，顾名思义，它将为您获取默认参数

string ParamDefaultValue = ""; 
SqlCommand cmdSPParamValue = 
    new SqlCommand("_GetParamDefault", conSPParamValue); 

cmdSPParamValue.CommandType = CommandType.StoredProcedure;

此存储过程的代码如下

CREATE proc _GetParamDefault
@Procname varchar(50),
@ProcParamName varchar(50),
@DefaultValue varchar(100) OUTPUT
as
/*
This procedure will return DEFAULT value for 
the parameter in thestored procedure.
Usage:
declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',
                                            @value OUTPUT
SELECT @VALUE

*****************************************************
Author: Eva Zadoyen
Edited: Rafael Mizrahi
*/

set nocount on
declare @sqlstr nvarchar(4000),
@obj_id int,
@version int,
@text varchar(8000),
@startPos int,
@endPos int,
@ParmDefinition NVARCHAR(500)
select @procName = rtrim(ltrim(@procname))
set @startPos= charindex(';',@Procname)

if @startPos<>0
begin
set @version = substring(@procname,@startPos +1,1)
set @procname = left(@procname,len(@procname)-2)
end
else
set @version = 1

SET @sqlstr =N'SELECT @text_OUT = (SELECT text FROM syscomments
WHERE id = object_id(@p_name) and colid=1 and number = @vers)'
SET @ParmDefinition = N'@p_name varchar(50),
@ParamName varchar (50),
@vers int,
@text_OUT varchar(4000) OUTPUT'

EXEC sp_executesql
@SQLStr,
@ParmDefinition,
@p_name = @procname,
@ParamName = @ProcParamName,
@vers = @version,
@text_OUT =@text OUTPUT

--select @TEXT
select @startPos = PATINDEX( '%' + @ProcParamName +'%',@text)
if @startPos<>0
begin
select @text = RIGHT ( @text, len(@text)-(@startPos +1))
select @endPos= CHARINDEX(char(10),@text) -- find the end of a line
select @text = LEFT(@text,@endPos-1)
-- check if there is a default assigned and 
-- parse the value to theoutput
select @startPos= PATINDEX('%=%',@text)
if @startPos <>0
begin
select @DefaultValue = 
     ltrim(rtrim(right(@text,len(@text)-(@startPos))))
select @endPos= CHARINDEX('--',@DefaultValue)
if @endPos <> 0
select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))

select @endPos= CHARINDEX(',',@DefaultValue)
if @endPos <> 0
select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))
end
ELSE
select @DefaultValue = 'NO DEFAULT SPECIFIED'
end
else
SET @DefaultValue = 'INVALID PARAM NAME'

set nocount off
return
GO

然后，您将参数添加到此命令，这些参数是从“主”连接派生的。 这些是您当前正在检查的过程的名称及其参数

cmdSPParamValue.Parameters.Add("@Procname",
                             myReader.GetString(0));
cmdSPParamValue.Parameters.Add("@ProcParamName", 
                             myReader.GetString(1));

最后，您指定输出类型并执行查询

SqlParameter param = cmdSPParamValue.Parameters.Add(
                    "@DefaultValue", SqlDbType.VarChar,100);
param.Direction = ParameterDirection.Output;
cmdSPParamValue.ExecuteNonQuery(); 
ParamDefaultValue = param.Value.ToString();

现在您有了参数的默认值，您可以在将来的存储过程调用中非常愉快地使用它！

此例程的完整源代码（使用 C#）如下所示

// open two connections 
string ConnectionString = "Data Source=localhost;Initial 
                           Catalog=Northwind;user id=sa"; 
SqlConnection conSP = new SqlConnection(ConnectionString); 
SqlConnection conSPParamValue = new SqlConnection(ConnectionString); 
conSP.Open(); 
conSPParamValue.Open(); 

// get all stored procedures and parameters 
SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP); 
cmdSP.CommandType = CommandType.StoredProcedure; 

// loop on all stored procecdures and parameters 
SqlDataReader myReader = cmdSP.ExecuteReader(); 
while (myReader.Read()) 
{ 
  if (myReader.GetString(0).ToLower().StartsWith("dt_") != true) 
  { 

    string ParamDefaultValue = ""; 
    SqlCommand cmdSPParamValue = new SqlCommand(
                         "_GetParamDefault", conSPParamValue); 
    cmdSPParamValue.CommandType = CommandType.StoredProcedure; 
    cmdSPParamValue.Parameters.Add("@Procname",
                               myReader.GetString(0)); 
    cmdSPParamValue.Parameters.Add(
             "@ProcParamName", myReader.GetString(1)); 

    SqlParameter param = cmdSPParamValue.Parameters.Add(
                         "@DefaultValue", SqlDbType.VarChar,100); 

    param.Direction = ParameterDirection.Output; 

    cmdSPParamValue.ExecuteNonQuery(); 

    ParamDefaultValue = param.Value.ToString(); 

    if (ParamDefaultValue != "NO DEFAULT SPECIFIED") 
    { 
      Console.WriteLine("{0}, {1}, {2}", myReader.GetString(0),    
                         myReader.GetString(1), ParamDefaultValue); 
    } 
  } 
} 
myReader.Close(); 
conSP.Close(); 
conSPParamValue.Close();

在此知识库中，您获得了一个新的开发工具：一种自动迭代数据库中所有存储过程并提取使用默认值声明的参数列表的方法。 使用此列表，您可以然后浏览您的代码，找出存储过程的使用位置，并确保 SqlCommand 填充了所有参数，包括可选参数。