SQL parameterization like in Query

-- 1. Construct a dynamic Transact-SQL method using the in clause to query numbers
-- A. The field type to be queried is Numeric.
-- List of queried values
Declare @ idlist varchar (100)
Set @ idlist = '1, 2, 3'
-- Concatenate and execute dynamic Transact-SQL statements
Exec ('select * From tbname where fdname in ('+ @ idlist + ')')
Go
-- B. The field type to be queried is struct.
-- The string boundary has been added to the list of queried values.
Declare @ idlist varchar (100)
Set @ idlist = ''a', ''B ', ''a '''
-- Concatenate and execute dynamic Transact-SQL statements
Exec ('select * From tbname where fdname in ('+ @ idlist + ')')
Go
-- The list of queried values does not have a string boundary.
Declare @ idlist varchar (100)
Set @ idlist = 'a, B 'A, C'
-- Because the field type is yes, the string boundary character (') must be added to the concatenation (')
Declare @ s varchar (1000)
Set @ s = ''''
+ Replace (replace (@ idlist ,'''',''''''),',',''',''')
+ ''''
-- Concatenate and execute dynamic Transact-SQL statements
Exec ('select * From tbname where fdname in ('+ @ s + ')')
Go
/* ===================================================== ===================== */
-- 2. Use like or patindex to query numbers
-- List of queried values
Declare @ idlist varchar (100)
Set @ idlist = '1, 2, 3'
-- Query
Select * From tbname where charindex (',' + rtrim (fdname) + ',' + @ idlist + ',')> 0
Select * From tbname where patindex ('%,' + rtrim (fdname) + ', %', ',' + @ idlist + ',')> 0
Select * From tbname where ',' + @ idlist + ', 'like' %,' + rtrim (fdname) + ', %'
Go
/* ===================================================== ===================== */
-- 3. Common Errors in number query
-- A. The easiest mistake: The expression acts as the expression list.
Declare @ s varchar (100)
Set @ s = '1'
Select ID, name from sysobjects where ID in (@ s)
/* -- Result
ID name
----------------------------
1 sysobjects
--*/
Set @ s = '1, 2, 3'
Select ID, name from sysobjects where ID in (@ s)
/* -- Result
Server: Message 245, level 16, status 1, Row 3
A syntax error occurs when you convert varchar values '1, 2, 3 'to an int-type column.
--*/
Go
-- B. The data type is ignored when a dynamic Transact-SQL statement is generated.
Declare @ s varchar (100)
Set @ s = 'U, s'
Exec ('select ID, name from sysobjects where ID in ('+ @ s + ')')
/* -- Result:
Server: Message 207, level 16, status 3, Row 1
The column 's' is invalid.
Server: Message 207, level 16, status 1, Row 1
The column 'U' is invalid.
--*/
Go
-- C. The accuracy of comparison is ignored.
-- Data to be queried
Declare @ t table (COL varchar (10 ))
Insert @ t select '1'
Union all select '11'
Union all select '20180101'
Union all select '22'
-- Query
Declare @ s varchar (100)
Set @ s = '2014, 22'
Select * From @ t where charindex (COL, @ s)> 0
/* -- Result
Col
----------
1
11
111
22
-*/
Go
In C #, ASP. NET, question about how like in achieves parameterized query. For common SQL statements such as select, the normal parameterized statement format is as follows:
Select * From profile where employeeid = @ employeeid
For example:
Tring loginstring = "select * From profile where employeeid = @ employeeid"
Ut please attention to the like SQL sentence:
Select * From profile where employeeid like '%' + @ employeeid + '% ';
The accurate search format is:
Select * From profile where employeeid like + @ employeeid;
So
String = "select * from box where boxid like '%' + @ substring + '% '"
Provides valuable information for this article. Article Include:
C # SQL like Parameter

The significance of parameterization is to provide the corresponding value from the parameter. For the like statement, the value after like includes all the parts in single quotes, including the percent sign (% ), therefore, when parameterizing the like value, you should move the percentage sign to the parameter value, as shown in the following code:
Cmd. Parameters ["@ keyword"]. value = "%" + strkeyword + "% ";
Do not look like this in SQL statements:
Select * from [tablename] Where [column1] Like '% @ keyword %'
No error is reported, but you cannot query the expected results.
Our general thinking is:
Like parameter:
String strsql = "select * From person. Address where city like '% @ add % '";
Sqlparameter [] parameters = new sqlparameter [1];
Parameters [0] = new sqlparameter ("@ Add", "Bre ");
In Parameters
String strsql = "select * From person. Address where addressid in (@ add )";
Sqlparameter [] parameters = new sqlparameter [1];
Parameters [0] = new sqlparameter ("@ Add", "343,372,114, 11533, 11535,11755, 11884,12092, 12093,12143 ");
However Program It cannot be executed. Even if no error is reported, no results can be found,
There is no clear answer for searching online. After repeated experiments, the problem is finally solved.
The correct solution is as follows:
Like Parameter
String strsql = "select * From person. Address where city like '%' + @ add + '% '";
Sqlparameter [] parameters = new sqlparameter [1];
Parameters [0] = new sqlparameter ("@ Add", "Bre ");
In Parameters
String strsql = "Exec ('select * From person. Address where addressid in ('+ @ add + ')')";
Sqlparameter [] parameters = new sqlparameter [1];
Parameters [0] = new sqlparameter ("@ Add", "343,372,114, 11533, 11535,11755, 11884,12092, 12093,12143 ");

 

Source: http://blog.csdn.net/pittroll/article/details/6641054