0

As the title says, I'm trying to execute a stored procedure with C# but I'm always getting an error

Invalid column name

The thing is that, if I execute it directly in SQL Server Management Studio with the same parameters, it just runs fine and I get the expected results.

The exception says that the error is on these specific lines of my procedure:

UPDATE #TBL_FINAL_RESULT
SET INDICATOR = Y.INDICATOR
FROM #TBL_FINAL_RESULT X
INNER JOIN (
            SELECT
            Z.Code,
            CASE 
                WHEN Z.M_MonthlyDeviation < 0 
                    THEN (
                            CASE
                                WHEN Z.M_MonthlyDeviation < Z.HighReference THEN '8'
                                WHEN Z.M_MonthlyDeviation > Z.LowReference THEN '9'
                                ELSE '6'
                            END
                        )
                    ELSE
                        '9'
                    END AS Indicator
            FROM (
                    SELECT
                    A.Code,
                    A.M_MonthlyReal,
                    A.M_MonthlyDeviation,
                    -1 * (A.M_MonthlyReal * 5 /100) as HighReference,
                    -1 * (A.M_MonthlyReal / 100) as LowReference
                    FROM #TBL_FINAL_RESULT A
                    WHERE A.NivelGRD > 1
            ) Z

)Y ON X.CODE = Y.CODE

This is my C# function that executes the stored procedure:

public DataTable ExecuteStoredProcedure_Query(string storedProcedure, List<SqlParameter> parametros = null)
    {
        DataTable table = new DataTable();

        using (var conn = Connection)
        {
            using (var cmd = new SqlCommand(storedProcedure, Connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if(parametros != null && parametros.Count > 0)
                {
                    foreach (var param in parametros)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                try
                {
                    conn.Open();
                    adapter.Fill(table);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        return table;
    }

And it's throwing the following exception:

Invalid column name 'NivelGRD'
Invalid column name 'Code'
Invalid column name 'M_MonthlyReal'
Invalid column name 'M_MonthlyDeviation'
Invalid column name 'M_MonthlyReal'
Invalid column name 'M_MonthlyReal'
Invalid column name 'CODE'

I'm completely sure those columns exist in the temp table. They are used in other parts of the procedure and they work fine, until that point.

What do you think is the problem?

1 Answer 1

0

Probably #TBL_FINAL_RESULT at runtime is not the temp table you think it is. If a temp table of that name is created in a top-level batch or in a stored procedure higher in the stack it will be visible in the stored procedure. And it may have different columns.

Sign up to request clarification or add additional context in comments.

2 Comments

#TBL_FINAL_RESULT is created in the same stored procedure at the beggining. So I'm sure the columns exist and their name is correct.
Then can you add a simple, self-contained repro to your question?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.