Cast a @param in SQL Server stored procedure?

Question

I have a SQL Server stored procedure that takes a @columnName parameter, and I am using it in building the query:

AND @columnName LIKE '%' + @userInput + '%'

If I replace the @columnName with a static value it works, like this:

AND userName LIKE '%' + @userInput + '%'

I believe this is because the @columnName is treated as string, because when running the stored procedure, it is being passed as string like that:

 EXEC app.findUsers 'UserName', 'sa'

Is it possible to do it without using a dynamic query string?

Answer 1

You need to use SQL Dynamic to do this.

DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT Column FROM Table WHERE [' + @columnName + '] LIKE ''%' + @userInput  + '%'''
PRINT @SQL
EXEC(@SQL)