When parameterized query executes it does not seem to have a value for the parameter

Question

The following code as I understand it should create a parameterized statement and adds a value to that parameter. The parameter being "@exp" and the value being added to it is determined by user input. I store the user input in String exp; (Not my choice someone else wrote the majority of this function).

SQl_Command.CommandText = "SELECT COUNT(*) As MyCount FROM members WHERE ([Primary Exp] = '@exp') AND ([Approved] = 'True') OR ([Approved] = 'True') AND ([Secondary Exp] = '@exp')";
SQl_Command.Parameters.Add("@exp", SqlDbType.NVarChar, 255);
SQl_Command.Parameters["@exp"].Value = exp;

The desired result for my test case is 16 but this query returns 0. The query works when running from the SQL Server 2008 and looks like this.

SELECT COUNT(*) As MyCount 
FROM members 
WHERE ([Primary Exp] = 'Risk Management') 
  AND ([Approved] = 'True') OR ([Approved] = 'True') 
  AND ([Secondary Exp] = 'Risk Management')

I should be able to use Response.Write(SQl_Command.CommandText); where I have selected "Risk Management" and the parameterized query should print out:

SELECT COUNT(*) As MyCount 
FROM members 
WHERE ([Primary Exp] = 'Risk Management') 
  AND ([Approved] = 'True') OR ([Approved] = 'True') 
  AND ([Secondary Exp] = 'Risk Management')`

It however does not. It leaves @exp as if that were the value I wanted to compare [Primary Exp] and [Secondary Exp] to. Is that correct?

I have seen multiple ways of doing this but I have in general been following the example here

I don't think this is the problem but this is how I use the query.

SQl_Reader = SQl_Command.ExecuteReader();
int numRows = 0;
while(SQl_Reader.Read()){
    numRows = Convert.ToInt32(SQl_Reader["MyCount"]);
}

I'm sure it is something simple I am missing but I havn't been able to see it.

Your expectation is wrong - using parameters doesn't mean that the client-side SQL code will **replace** those parameters with their actual values. The parameters are sent **as parameters** to the SQL Server, including their values in a list of values. This is the way it is and the way it's designed to be - if you expected the parameters to be replaced with their actual values before the query is sent to SQL Server, you were wrong. — marc_s, Aug 22 '13 at 05:02

score 6 · Answer 1 · answered Aug 21 '13 at 23:36

You're dealing here with variables, the SQL objects don't simply perform a "safer" search-and-replace. Change your code to:

SQl_Command.CommandText = "SELECT COUNT(*) As MyCount FROM members WHERE 
        ([Primary Exp] = @exp) AND ([Approved] = 'True') OR
        ([Approved] = 'True') AND ([Secondary Exp] = @exp)";

and you should find you now get the correct value; with your original version, it would literally have searched for the string '@exp'.

Note that the SQL parameters and the command itself are only combined during execution, as part of the call into SQL Server. So when you saw @exp still showing in CommandText even after the parameters have been added, this was in fact correct.

score 1 · Answer 2 · answered Aug 21 '13 at 23:37

When you run a parameterized query via one of the .NET SQL Server providers, the query keeps it parameterized form all the way to the server. If you were to run SQL Profiler while executing your query, you'd see an "RPC" event occurring that executes your query, filling in the parameters along the way.

RPC is one way for a SQL client to directly execute a stored procedure by name, as opposed to executing a T-SQL statement as text. Instead of sending your SQL command as T-SQL, it's sent in the form of an RPC call to "sp_executesql", with your query and the subsequent parameters being themselves sent as parameters to sp_executesql. So your query would actually run as something like:

sp_executesql "SELECT COUNT(*) As MyCount FROM members WHERE ([Primary Exp] = '@exp') AND ([Approved] = 'True') OR ([Approved] = 'True') AND ([Secondary Exp] = '@exp')", "@exp NVARCHAR(255)", "@exp = 'Risk Management'"

The difference is explained in more detail in this article about SQL Server Connection Basics.

When parameterized query executes it does not seem to have a value for the parameter

2 Answers2