C# getting error calling stored procedure with an output variable, parameter not supplied

Question

I'm calling a generalized method in C# that returns a string from an Azure SQL stored procedure with an output variable. The error is:

Procedure or function 'TableScriptGenerate' expects parameter '@table', which was not supplied

I'm passing the parameters as Lists My code looks like:

    static public string ReadSpData(string cnxn, string storedProcedure,List<string> paramName,List<string> paramValue, List<string> paramType, List<int> paramSize, List<string> paramDir)
    {
        string rtn = "";
        int i = 0;

        try
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();

            DbProviderFactory dbf = DbProviderFactories.GetFactory("System.Data.SqlClient");
            DbConnection dbcn = dbf.CreateConnection();
            dbcn.ConnectionString = cnxn;

            dbcn.Open();

            DbCommand dbcmd = dbcn.CreateCommand();

            for (i = 0; i < paramValue.Count; i++)
            {
                SqlDbType sdt = SqlDbType.NVarChar;

                switch (paramType[i])
                {
                    case "varchar":
                        sdt = SqlDbType.VarChar;
                        break;

                    case "char":
                        sdt = SqlDbType.Char;
                        break;

                    case "int":
                        sdt = SqlDbType.Int;
                        break;
                }

                sqlParam.Add(new SqlParameter(paramName[i], sdt, paramSize[i]));
                sqlParam[sqlParam.Count - 1].ParameterValue = paramValue[i]; //edited

                switch (paramDir[i].ToLower())
                {
                    case "input":
                        sqlParam[sqlParam.Count - 1].Direction = ParameterDirection.Input;
                        break;

                    case "output":
                        sqlParam[sqlParam.Count - 1].Direction = ParameterDirection.Output;
                        break;

                    case "return":
                        sqlParam[sqlParam.Count - 1].Direction = ParameterDirection.ReturnValue;
                        break;
                }

                dbcmd.Parameters.Add(sqlParam[sqlParam.Count - 1]);
            }

            dbcmd.CommandType = CommandType.StoredProcedure;
            dbcmd.CommandText = storedProcedure;

            dbcmd.ExecuteNonQuery();

            rtn = dbcmd.Parameters["@table_definition"].ToString(); //edited
        }

storedProcedure = TableScriptGenerate
paramValue = 'path_person', 1, @tab
paramType = nvarchar, char, nvarchar
paramSize = 4000,1,4000
paramDir = input, input, output

The stored procedure being called looks like:

ALTER PROCEDURE [dbo].[TableScriptGenerate]
    (@table nvarchar(4000) = NULL,
     @isExternal char = NULL,
     @table_definition nvarchar(4000) = NULL OUTPUT)
AS
BEGIN
    ...
    SET @table_definition = CONCAT(@preTbl, @sql, @post, '~/n')
END

I've added the initialization of input params, reading that they might help but they didn't. When I look at dbcmd.Parameters in the debugger, it shows all three params, their names, sizes, types, etc. properly.

So, what am I doing wrong? Why does it still not see the @table parameter?

This is a really bad way of going about creating parameters. If you want this thing to receive a list of parameters, just pass in a IDbParameterCollection with your parameters defined in the caller. You've missed a parameter in the caller. — Paul Alan Taylor, Dec 03 '20 at 13:31
mjwills: I wasn't, just generic param0, param1, etc. I added the names and now it works... sort of. I'm not getting an error but I'm not getting the proper output either, just "~\n" which looks like the last bit of what it's supposed to return. Paul: What you see was the result of trying everything I could think of. I'll go back to the IDdParameterCollection. — Velocedge, Dec 03 '20 at 13:37
Ok, my bad. I wasn't setting the "Value" of SqlParameter in all my mucking about trying different things. So, the long and short of it was mjwills' comments about specifying the @table parameter. How do I specify that his was the answer? — Velocedge, Dec 03 '20 at 16:56
@Velocedge May I help him post it as answer? Then your problem won't be closed. — Leon Yue, Dec 04 '20 at 02:05

score 0 · Accepted Answer · answered Dec 04 '20 at 02:11

Really thanks for @mjwills comment and guide. From the error message, the parameter @table was not supplied.

Your followed his suggestion and find that you didn't set the "Value" of SqlParameter in all my mucking about trying different things.

We are glad to hear that the issue is solved. I help post this as answer and this can be beneficial to other community members.

C# getting error calling stored procedure with an output variable, parameter not supplied

1 Answers1