SQL Server - Building dynamic query

[marlind]

I want to be able to build a query with a variable amount of where statements. A user will be adding various conditions. Is there a way to make a query flexible? In Microsoft access I would write visual basic code and have it loop thru and add the various where statements. I am new to SQL server so excuse me if I don't have the terminology correct. Thanks for the help.

[ck9663]

I want to be able to build a query with a variable amount of where statements. A user will be adding various conditions. Is there a way to make a query flexible? In Microsoft access I would write visual basic code and have it loop thru and add the various where statements. I am new to SQL server so excuse me if I don't have the terminology correct. Thanks for the help.

if you have a GUI, you can build the string in your GUI then execute the through Execute Method of your front-end tool the same way you to in MS Access. if you're planning to use stored procedure you can build your query by something like:

set @sqlstringvaria ble = 'select field from table'
set @sqlstringvaria ble = @sqlstringvaria ble + ' where ' + @condition1
set @sqlstringvaria ble = @sqlstringvaria ble + ' and ' + @condition2

then do a

exec ( @sqlstringvaria ble)