构造使用IN子句的动态Transact-SQL方法进行编号查询

原帖：http://blog.csdn.net/htl258/archive/2009/04/10/4062358.aspx

 

-- 1. 构造使用IN子句的动态Transact-SQL方法进行编号查询

-- a. 要查询的字段类型是数字型

-- 查询的值列表
DECLARE @idlist varchar ( 100 )
SET @idlist = ' 1,2,3 '

-- 拼接并执行动态Transact-SQL语句
EXEC ( ' SELECT * FROM tbname WHERE fdname IN( ' + @idlist + ' ) ' )
GO

-- b. 要查询的字段类型是字符型
-- 查询的值列表已经加上了字符串边界符
DECLARE @idlist varchar ( 100 )
SET @idlist = ''' a '' , '' b '''' a '' , '' c '''

-- 拼接并执行动态Transact-SQL语句
EXEC ( ' SELECT * FROM tbname WHERE fdname IN( ' + @idlist + ' ) ' )
GO

-- 查询的值列表没有字符串边界符
DECLARE @idlist varchar ( 100 )
SET @idlist = ' a,b '' a,c '

-- 由于是字段类型是,所以在拼接时,必须为其加上字符串边界符(')
DECLARE @s varchar ( 1000 )
SET @s = ''''
    + REPLACE ( REPLACE ( @idlist , '''' , '''''' ), ' , ' , ''' , ''' )
    + ''''

-- 拼接并执行动态Transact-SQL语句
EXEC ( ' SELECT * FROM tbname WHERE fdname IN( ' + @s + ' ) ' )
GO

/* ===================================================== */


-- 2. 使用LIKE或者PATINDEX进行编号查询
-- 查询的值列表
DECLARE @idlist varchar ( 100 )
SET @idlist = ' 1,2,3 '

-- 查询
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. 编号查询中常见的错误
-- a. 最容易犯的错误：表达式充当表达式列表。
DECLARE @s varchar ( 100 )
SET @s = ' 1 '
SELECT id,name FROM sysobjects WHERE id IN ( @s )
/* --结果
id          name
---------------- ------------
1           sysobjects
-- */

SET @s = ' 1,2,3 '
SELECT id,name FROM sysobjects WHERE id IN ( @s )
/* --结果
服务器: 消息 245，级别 16，状态 1，行 3
将 varchar 值 '1,2,3' 转换为数据类型为 int 的列时发生语法错误。
-- */
GO

-- b. 生成动态Transact-SQL语句时忽略了数据类型。
DECLARE @s varchar ( 100 )
SET @s = ' U,S '
EXEC ( ' SELECT id,name FROM sysobjects WHERE id IN( ' + @s + ' ) ' )
/* --结果:
服务器: 消息 207，级别 16，状态 3，行 1
列名 'S' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'U' 无效。
-- */
GO

-- c. 忽略了比较的精确性问题。
-- 要查询的数据
DECLARE @t TABLE (col varchar ( 10 ))
INSERT @t SELECT ' 1 '
UNION ALL SELECT ' 11 '
UNION ALL SELECT ' 111 '
UNION ALL SELECT ' 22 '

-- 查询
DECLARE @s varchar ( 100 )
SET @s = ' 111,22 '
SELECT * FROM @t WHERE CHARINDEX (col, @s ) > 0
/* --结果
col       
----------
1
11
111
22
- */
GO