CODEWITHSUNDEEP
sql-serversql-server-2008
Jignesh.Raj
Jignesh.Raj

Reputation: 5987

Create Stored Procedure with column Name & Value For Dynamic Search

I want to create a stored procedure with two parameters ColumnName and ColumnValue for searching

My query would look something like this:

SELECT * FROM TableName WHERE @ColumnName Like  @ColumnValue  + '%'

but it's return 0 result

Upvotes: 1

Views: 704

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

You need Dynamic SQL for that. What you have written is searching the @columnValue value in the @ColumnName value. So if your column is foo and the value is bar it searches if 'foo' LIKE 'bar%'

DECLARE @SQL AS NVARCHAR(MAX)

SET @SQL = 
N'SELECT * FROM TableName 
WHERE ' + QuoteName(@ColumnName) + ' LIKE @val+''%'''

EXECUTE sp_executesql 
@SQL,
N'@val varchar(100)',
@val = @ColumnValue;

Upvotes: 4

Related Questions