How to parameterize query with like operator in Sql Server

Question

I am trying to parameterize Sql Query with like operator but it's not working. Both Search Criteria and Search text will be through Variable. Here what I am doing

declare @Query nvarchar(500), @SearchBy nvarchar(50) = 'PtName', @SearchText nvarchar(50) = 'Sh'
set @Query = 'select Id, PtName, Y, M, D, Sex, PtCode, ReceiptDate, ReferBy, RefDrCd, PtTitle, NetAmount, IncentiveAmount, PaidAmount, DueAmount, Investigation, LabCode, LabName, DiscAmount, PNo  FROM DiagMain where '+@SearchBy+' like '+@SearchText+' %'
    exec(@Query)

Please Help.

It would help us if you gave the error message it's giving. It would help you if you first print any SQL you've assembled by string concatenation and see if it looks reasonable. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 16, 2017 at 8:52

John M · Accepted Answer · 2022-10-24 12:38:11Z

4

DECLARE @Query NVARCHAR(500), @SearchBy NVARCHAR(50) = 'PtName', @SearchText NVARCHAR(50) = 'Sh'
SET @Query = 'select Id, PtName, Y, M, D, Sex, PtCode, ReceiptDate, ReferBy, RefDrCd, PtTitle, NetAmount, IncentiveAmount, PaidAmount, DueAmount, Investigation, LabCode, LabName, DiscAmount, PNo  
FROM DiagMain where '+@SearchBy+' like '''+@SearchText+'%'''

PRINT @Query
EXEC(@Query)

edited Oct 24, 2022 at 12:38

John M

14.7k29 gold badges94 silver badges143 bronze badges

answered Aug 16, 2017 at 8:51

Alfaiz Ahmed

1,7261 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alfaiz Ahmed Over a year ago

@shaiwal Tripathi IN Dynamic SQL query for String Variable Closing Should be '''variable Name ''' Like this and Print The String_SQL to Check whether The query you Execute Is Proper or Not. it will you out in Solving Issue

Alfaiz Ahmed Over a year ago

if it's work's for you can vote And Accept The Answer

TT. Over a year ago

I'd immediately search for ';DROP TABLE DiagMain;--. Good luck.

Collectives™ on Stack Overflow

How to parameterize query with like operator in Sql Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related