I have declared two variables in RAW sql
DECLARE @str nvarchar(max), @str1 nvarchar (max);
SET @str = " AND (c.BondSales_Confirmed <> -1)";
SET @str1 = " AND (c.BondSales_IssueType = 'REGULAR')";
My SQL query is:
SELECT * From t_BondSales Where (BondSales_cType <> 'Institute') " + str1 + str "
Here I get the following error:
Error: SQL Problems: Incorrect Syntax near "+ str1 + str"
Can any one Please help me with the proper syntax about how to concat String in where clause?
very easy!! in mysql use CONCAT() function:
SELECT * FROM tbl_person WHERE CONCAT(first_name,' ',last_name) = 'Walter White';
but this does not work in mysql:
SELECT * FROM tbl_person WHERE first_name+' '+last_name = 'Walter White';
CONCAT(first_name, ' ', last_name) = '' one works with JPA TypedQuery too. Thank you for your help.
Try this one -
DECLARE
@str NVARCHAR(MAX)
, @str1 NVARCHAR (MAX);
SELECT
@str = ' AND c.BondSales_Confirmed != -1'
, @str1 = ' AND c.BondSales_IssueType = ''REGULAR''';
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT *
FROM t_BondSales
WHERE BondSales_cType != ''Institute'''
+ @str
+ @str1
PRINT @SQL
EXEC sys.sp_executesql @SQL
Passing column names along with values is subject to SQL Injection. Make sure to read this post www.sommarskog.se/dynamic_sql.html
So I would suggest you to change the code like this
declare @BondSales_Confirmed int
declare @BondSales_IssueType varchar(100)
SELECT * From t_BondSales Where (BondSales_cType <> 'Institute')
AND (c.BondSales_Confirmed <> @BondSales_Confirmed or @BondSales_Confirmed is null)
AND (c.BondSales_IssueType = @BondSales_IssueType or @BondSales_IssueType is null)
Just pass null value if you do not want to apply a condition to the columns BondSales_Confirmed and BondSales_IssueType
