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'.
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.
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
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%'
%should be inside the string.sp_executesqlprotects you from SQL injection. It does not. It would have if you used parameters when calling it.