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?