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?

Answer 1

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.