SQL server name of columns as variables

Question

I have the following statement in a stored procedure. I am passing the name of the column as parameter and also the value to be checked in another variable. Is it possible to accomplish this in SQL server. Please let me know.

---

  SELECT CaseId FROM app_Case 
  where @SearchCat=@keywords
  ORDER BY CreatedDate DESC

Answer 1

I think the only way to do this would be to generate a dynamic SQL statement. The other option would be to take all column values as parameters, default them to null, and check for that.

ie

WHERE (cola = @cola OR @cola IS NULL) AND (colb = @colb OR @colb IS NULL) etc.

Answer 2

You need to create a string of SQL inside the SP and execute it.

Declare @SQL As VARCHAR(8000)

SET @SQL = 'SELECT CaseId FROM app_Case where ' + 
           @SearchCat + ' = '' + @keywords + 
           '' ORDER BY CreatedDate DESC'

EXEC(@SQL)

Answer 3

You can build a dynamic query Essentially you build a string and then execute it. (Watch out for SQL injection attacks).

Another approach would be to use a case statement which if you don't have a lot of options might be worth trying:

select CaseId from app_Case
where case when @searchCat='field1'
         then field1 
         else @searchVal 
      end = @searchVal and
      case when @searchCat='field2' 
         then field2 
         else @searchVal 
      end = @searchVal

Another approach is do the same thing using or clauses:

   select CaseId from app_Case
   where (@searchCat='Field1' and Field1=@searchVal) OR
         (@serachCat='Field2' and Field2=@searchVal)