Maybe I'm not understanding but, in that case, what is it that you want to do differently?

What I usually is have a form that contains selection criteria for any fields the user might want to use to filter a report. I pass all those parameters to a stored procedure where most of them have a null option. Then, in the stored procedure I create a variable for the sql command, like @ExecCmd, that's initialized to 'SELECT [field1], [field2], ... FROM [Projects] WHERE 1=1'
From there, you just have a series of IF statements:

IF field1 IS NOT NULL
SET @ExecCmd = @ExecCmd + ' AND field1 = @Field1'
Then you just use sp_sqlexec to execute the command statement.



>>>>>>Hi everyone, I could use some advice on this.
>>>>>>We can define a select statement like this

>>>>>>Dim SQL As String = "SELECT [tipid], [rtaid], [projectname], [location], [shortdesc], [sponsor] FROM [projects] WHERE (archived = @aFilter) ORDER BY [sponsor], [projectname]"
>>>>>>Dim oCommand As New SqlCommand(SQL, oConn)
>>>>>>oCommand.Parameters.AddWithValue("aFilter", somevalue)

but what do we do when we need WHERE (@acomplicatedstring) which may not involve the archived field but may or may not involve others? Is it appropriate to build the string by concatenation and not use parameters in this case? I am trying to allow the user to build a query that allows any number of variables.
>>>>>>
>>>>>>Thanks
>>>>>
>>>>>Instead of building a query that has the parameter values in it, you could set parameters for all fields you might need. Set the uneeded parameters to null and ignore them in the command..
>>>>>
>>>>>ie..
>>>>>
>>>>>where (@param1 is null or field1=@param1) and (@param2 is null or field2=@param2)...
>>>>
>>>>This is not a good way to write a query for performance reasons. Please check Erland's blog I quoted earlier.
>>>
>>>Thanks Naomi. That give me a little vision on how to do this. Let me ask another related question. Suppose we have the user build this complex "filter" using these techniques. Is there a good way to persist this filter across several pages?
>>>(I'm still hung on the VFP way of thinking.)
>
>
>>This looks like a good candidate for building a stored procedure that builds a command string based on parameters that contain the user's selection criteria and then uses sp_sqlexec to execute the string. If you want to do that on the .NET side of things, you could use StringBuilder.
>>
>
>Thanks Linda. That's basically what I'm doing but in the code behind.