How to Concat String in SQL WHERE clause

Question

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?

Answer 1

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';

Answer 2

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