0

Statement:

declare @Searchkey varchar(50)='a'
declare @Sql nvarchar(max) =''

set @Sql = @Sql + 
    'select * from products where upper(ProductName) like %' + upper(@Searchkey) + '%'

execute sp_executesql @Sql

Error message:

Msg 102 Level 15 State 1 Line 1
Incorrect syntax near 'A'.

Zhorov
  • 28,486
  • 6
  • 27
  • 52
clint
  • 1
  • 3
    The like wildcard `%` should be inside the string. – jarlh May 15 '20 at 06:22
  • 1
    It would appear you believe that `sp_executesql` protects you from [SQL injection](https://stackoverflow.com/q/332365/11683). It does not. It would have if you [used parameters](https://stackoverflow.com/a/61813144/11683) when calling it. – GSerg May 15 '20 at 07:01

3 Answers3

1

The reason for this error is that you need to add quotes around the search pattern, when you build the dynamic statement. But I think that your should use parameter in this dynamically built statement to prevent SQL injection issues:

DECLARE @Searchkey varchar(50) = 'a'
DECLARE @Sql nvarchar(max) = N''

SET @Sql = 
    @Sql + 
    N'SELECT * FROM products WHERE UPPER(ProductName) LIKE CONCAT(''%'', UPPER(@Searchkey), ''%'')'

PRINT @Sql
EXEC sp_executesql @Sql, N'@Searchkey varchar(50)', @Searchkey
Zhorov
  • 28,486
  • 6
  • 27
  • 52
1

You do not require a dynamic query for such case, you can use below simple query to get the desired result.

declare @Searchkey varchar(50)= 'a'
select * from products where upper(ProductName) like  '%' + upper(@Searchkey) + '%'

If you still want to go with Dynamic query then below are the connect syntax.

declare @Searchkey varchar(50)='a'
declare @Sql nvarchar(max) =''

set @Sql =@Sql+'select * from products where upper(ProductName) like ''%' +upper(@Searchkey)+'%'''

--print @Sql
execute sp_executesql @Sql

Note: Whenever you will get an error with a dynamic query then the best way is to print the query using print statement that will help to identify the error easily. In your case, a single quote was missing.

Mukesh Arora
  • 1,763
  • 2
  • 8
  • 19
0

You're not placing quotes around your search term, so the literal query that's being sent is:

select * from products where upper(ProductName) like %A%

You need to wrap the search term in quotes, like so:

set @Sql =@Sql+'select * from products where upper(ProductName) like ''%'+upper(@Searchkey)+'%'''

This will create the following query:

select * from products where upper(ProductName) like '%A%'
user849924
  • 318
  • 3
  • 9