CODEWITHSUNDEEP
sqlsql-servervbaado
JMcD
JMcD

Reputation: 100

ADO Repeated SQL Parameters

I have a query that uses a sub query with the same parameters, so it looks something like this:

strSelect = _
"SELECT a.State, a.FirstName, a.LastName, b.JobTitle " & _
"FROM apples a " & _
"LEFT JOIN ( " & _
    "SELECT a.RecordNumber, " & _
    "CASE WHEN j.JobTitle IS NULL THEN j.JobTitle2 ELSE j.jobtitle END " & _
    "FROM apples a " & _
    "LEFT JOIN jobs j ON j.RecordNumber = a.RecordNumber " & _
    strWhere & _
    ") b ON b.RecordNumber = a.RecordNumber "

strWhere = _
"WHERE a.JobState = ? " & _
"AND a.JobDate = ? "

The sub-query in real life is less pointless than the one in the example.

The question here is if I repeat strWhere, then is there a way to get the parameters to work for the resulting strSQL = strSelect & strWhere statement? The issue is that I have two parameters, but they're repeated, so it would be four parameters for the strSQL statement, but I would prefer to not repeat my parameters when I declare them.

I have a feeling I'm missing something pretty obvious here. Any suggestions?

Upvotes: 0

Views: 188

Answers (1)

Liath
Liath

Reputation: 10191

You should use named parameters:

Try using named parameters for example:

WHERE a.JobState = @JobDate

and

AND a.JobDate = @JobDate

Then, when you add your parameters you will only need to add them once. It will look something like this:

SqlParameter param  = new SqlParameter();
param.ParameterName = "JobDate";
param.Value         = theDate;

Upvotes: 1

Related Questions