Searching with desired column name with value is not working

User 11762623

ALTER PROCEDURE [dbo].[SP_SearchByUserNeed] ( @ColumnName varchar(20), @Value Varchar(100) ) AS BEGIN BEGIN try Select * From Product where @ColumnName like @Value+'%'; END try BEGIN catch print('Error in [SP_SearchByProductName]') END catch END

Corporal Agarn

You cannot use a variable in this manner. You would need to use dynamic code.

Mongo: Mongo only pawn... in game of life.

User 11762623

Hay, Thank you so much for your valuable response. I am agree with you. But i can't find a way to do the replacement of the column name dynamically with a variable. If possible please explain the same. I believe. Thank you

GuyThiebaut

Try something like this - as I am on holiday I have not had a chance to test this:

ALTER PROCEDURE [dbo].[SP_SearchByUserNeed]
(
@ColumnName varchar(20),
@Value Varchar(100)
)
AS
BEGIN
BEGIN try
declare @qry nvarchar(max);
set @qry = 'Select * From Product where @ColumnName like ' + '''' + @Value + '%' + '''';
exec(@qry);
END try
BEGIN catch
print('Error in [SP_SearchByProductName]')
END catch
END

Hugs and kisses to the downvoter :rose:

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Corporal Agarn

To create dynamic T-SQL you start with a variable for the code

DECLARE @strSQL NVARCHAR(1000);
SET @strSQL = N'Select * From Product where ' + @ColumnName + ' like @Value+''%'';';
EXECUTE sp_executesql @strSQL, @Value

Hope this helps

Mongo: Mongo only pawn... in game of life.

Richard Deeming

That code is vulnerable to SQL injection. To execute dynamic parameterized queries within SQL, you need to use sp_executesql[^].

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Richard Deeming

Almost! You're missing the parameter definition from sql_executesql. I'd also be inclined to validate the column name before concatenating it.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Richard Deeming

This should work:

ALTER PROCEDURE dbo.SP_SearchByUserNeed
(
@ColumnName varchar(20),
@Value varchar(100)
)
AS
BEGIN
DECLARE @RealColumnName sysname;
DECLARE @Statement nvarchar(max);

-- Validate the column name:
SELECT
    @RealColumnName = name
FROM
    sys.columns
WHERE
    object\_id = OBJECT\_ID('Product')
And
    name = @ColumnName
;

If @RealColumnName Is Null
BEGIN
    RAISERROR('Unknown column: "%s"', 16, 1, @ColumnName);
    Return;
END;

SET @Statement = N'SELECT \* FROM Product WHERE ' + QuoteName(@RealColumnName) + N' Like @Value + ''%''';
EXEC sp\_executesql @Statement, N'@Value varchar(100)', @Value;

END

This will validate that the column name passed in is a valid column in the Product table, and avoid SQL Injection[^] in the dynamic query.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Corporal Agarn

It's early here. :-D

Mongo: Mongo only pawn... in game of life.