Ali
Ali

Reputation: 1759

Cast a @param in SQL Server stored procedure?

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?

Upvotes: 0

Views: 714

Answers (1)

ChrisFerreira
ChrisFerreira

Reputation: 172

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)

Upvotes: 1

Related Questions