T-SQL creating a dynamic where statement?

Question

I know this is not possible, but is there something that would work? Basically I want the where statement to be dynamic, allowing me to pass it any string, which it will be able to search upon.

Declare @search varchar(80)
set @search = 'RegionID'

Select * from TBL_TripDetails
Where @search = '1'

Thanks for your answers. After reading a few documents, I have decided to use multiple select statements instead of using dynamic sql. thanks!

Answer 1

declare @sql nvarchar(max);
set @sql = N'select * from table where ' + quotename(@search) + N'=''1''';
exec sp_executesql @sql;

See The Curse and Blessings of Dynamic SQL

Answer 2

Declare @search varchar(80)
set @search = 'RegionID'

declare @query varchar(max)
set @query = "Select * from TBL_TripDetails Where " + @search + " = '1'"
exec @query

Answer 3

It is indeed possible, altough is often frowned upon. Have a look at sp_executesql

Answer 4

DECLARE @search VARCHAR(80)
DECLARE @SQL VARCHAR(8000)

SET @search = 'RegionID'

SET @SQL = 'SELECT * FROM TBL_TripDetails WHERE ' + @search + ' = 1'
EXEC @SQL

Be careful though. Concatenating SQL can allow SQL injection attacks.

Answer 5

I'm a bit confused with your question "pass it any string, which it will be able to search upon". In your example your passing in a field which is being compared against a hard coded value of 1, this doesn't really match your description.

If this is truly what you wanted, then you'll need to use Dynamic SQL. If you just want to be able to support optional search criteria/parameters (e.g. If RegionID has a value set then apply criteria, else ignore criteria), then use the example below.

DECLARE @RegionID AS VARCHAR(1);

SELECT * 
FROM TABLE
WHERE (@RegionID Is Null OR @RegionID = '' OR RegionID = @RegionID);

Now, if @RegionID is blank or NULL it won't be used in the criteria.