使用C＃从Inline SQL存储的过程调用中提取参数及其值

Question

我有这样的sql命令文本（可能有更多参数）

dbo.storedProcedure @JobID = 1, @JobName = 'xyz'

我想在某种集合中获取参数值和名称，然后我可以循环浏览并将它们添加为SqlParameter s。

例如：

foreach (Param in parameters)
{
    cmd.Parameters.AddWithValue(param.Name, Param.Val)
}

然后，我将从命令文本（即dbo.storedProcedure）中删除参数。

我要这样做的原因是因为我想要该过程中的" returnValue"。我知道我只能使用 CommandType.Text并按照命令文本传递，但是当我这样做时，我总是得到0作为返回值。

如果我设置了CommandType = CommandType.StoredProcedure，并且还使用ReturnValue方向的参数，我从该过程中获取正确的返回值。

我发现这个非常接近，但只给了我参数名称。我也想要值。

c＃解析SQL语句使用参数

谢谢

Answer 1

这将列出所有存储的过程和功能及其参数。我怀疑您可以根据自己的需求量身定制

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
       SO.name AS [ObjectName],
       SO.Type_Desc AS [ObjectType (UDF/SP)],
       P.parameter_id AS [ParameterID],
       P.name AS [ParameterName],
       TYPE_NAME(P.user_type_id) AS [ParameterDataType],
       P.max_length AS [ParameterMaxBytes],
       P.is_output AS [IsOutPutParameter]
  FROM sys.objects AS SO
  INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
  WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE TYPE IN ('P','FN'))
  ORDER BY [Schema], SO.name, P.parameter_id

Answer 2

string commandText = "dbo.storedPorc @JobID=1, @JobName='xyz'"
string paramsX = commandText.Substring(commandText.IndexOf(' ') + 1);
Dictionary<string,string> parameters = new Dictionary<string,string>();
foreach(var s in paramsX.Split(','))
{
     var finalParams = s.Split('=');
     parameters.Add(s[0],s[1].Replace("'",string.Empty););
}

这个词典您可以以这种方式使用

foreach (KeyValuePair<string,string> param in parameters)
{
    cmd.Parameters.AddWithValue(param.Key, param.Value)
}

Answer 3

1）我会避免使用addWithValue，因为它会生成查询计划缓存polution

http://www.sqlpassion.at/archive/2015/07/20/how-to-pollute-your-plan-plan-cache-with-parameterized-sql-statement/

2）如果我必须将默认值传递给param一个简单解决方案是使用默认关键字，这样 Exec dbo.proc @param1=123,@param2=DEFAULT

在c＃/vb中，我将使用以下解决方案：您如何将"默认"指定为ado.net中的SQL参数值？

bassicaly，我不必知道默认值。

3）如果要解析过程源代码以找出这些默认值，则应使用tsqlscriptdom api（您可以从此示例开始https://blogs.msdn.microsoft.com/arvindsh/2013/04/04/using-the--transactsql-scriptdom-parser-to-parser-to-get-statement-counts/）