Extract parameters and their values from an inline sql stored procedure call using C#

Question

I have a SQL command text like this (there could be more parameters)

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

I want to get the parameter values and names in some kind of collection which I can then loop through and add them as SqlParameters.

For example:

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

I will then remove the parameters from the command text i.e. dbo.storedProcedure.

The reason I want to do this is because I want the 'ReturnValue' from the procedure. I know I could just use CommandType.Text and pass the command text as is but I always get 0 as the return value when I do it that way.

If I set CommandType = CommandType.StoredProcedure and also use a parameter with ReturnValue direction I get the correct return value from the procedure.

I found this which is very close but only gives me the parameter names. I also want the values too.

C# Parse SQL statement to use parameters

Thanks

I don't understand what you are trying to do here. Your command already has the parameters so adding them as actual parameter is going to cause problems. You will also need to modify the command text. And you will also need to modify the command type to be stored proc. I think you need to provide a bit more detail before we can really help. — Sean Lange, Jun 20 '16 at 16:20
Thanks Sean - I've updated the question to explain why I can't just use the command text as is. — user3781891, Jun 20 '16 at 16:24
If you are using the ReturnValue you are likely doing something wrong. The return from a stored procedure is used to indicate the status of the execution. If you want values out you should use OUTPUT parameters. — Sean Lange, Jun 20 '16 at 16:35
Hi sean, correct - I want to know if the proc succeeded or not. I guess I could use an output parameter instead. thanks — user3781891, Jun 21 '16 at 07:00

score 1 · Answer 1 · answered Jun 20 '16 at 16:21

This will list all stored procedures and functions along with their parameters. I suspect you can tailor to your needs

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

Pawan Nogariya · Answer 2 · 2016-06-20T16:28:34.620

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););
}

This this dictionary you can use in this way

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

score 0 · Answer 3 · edited May 23 '17 at 12:22

1) I would avoid AddWithValue because it generates query plan cache polution

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

2) If I have to pass default value to param one simple solution is to use DEFAULT keyword thus Exec dbo.proc @param1=123,@param2=DEFAULT

In C#/VB I would use following solution: How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?

Bassicaly, I don't have to know that default value.

3) If you want to parse procedure source code to find out those default values then you should use TSQLScriptDom API (you could start with this example https://blogs.msdn.microsoft.com/arvindsh/2013/04/04/using-the-transactsql-scriptdom-parser-to-get-statement-counts/)

Extract parameters and their values from an inline sql stored procedure call using C#

3 Answers3