SQL parameterization like in Query

Source: Internet
Author: User
Tags rtrim

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.