CODEWITHSUNDEEP
t-sqlvariables
dada
dada

Reputation:

SQL server name of columns as variables

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

Upvotes: 0

Views: 510

Answers (3)

JoshBerke
JoshBerke

Reputation: 67068

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)

Upvotes: 1

HardCode
HardCode

Reputation: 6756

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)

Upvotes: 1

atfergs
atfergs

Reputation: 1684

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.

Upvotes: 2

Related Questions