-
SQL code
-
-- 建立测试环境
IF object_id ( ' tb ' ) IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id INT IDENTITY ( 1 , 1 ),v VARCHAR ( 10 ))
GO
INSERT tb SELECT ' a '
UNION ALL SELECT ' b '
INSERT tb SELECT ' x '
UNION ALL SELECT ' z '
Go
-
SQL code
-
-- =**********************************************
-- 为什么我执行下面的语句选不到正确数据
CREATE PROC p
(
@field VARCHAR ( 10 ), -- 字段名
@value VARCHAR ( 10 ) -- 值
)
AS
SELECT * FROM tb WHERE @field = @value
GO
EXEC p ' v ' , ' a '
GO
DROP PROC p
GO
--为什么我执行下面语句报错
-
SQL code
-
CREATE PROC p
(
@table VARCHAR ( 10 ), -- 表名
@value VARCHAR ( 10 ) -- 值
)
AS
SELECT * FROM @table WHERE v = @value
说明:
在这二个存储过程中，@table,@field,@value都被定义为varchar
第一个实际上执行的是两个变量的比较，它的作用相当于
IF @field=@value
SELECT * FROM tb
语法未错，意思上却大错特错
第二个实际上执行的是
SELECT * FROM 一个字串 WHERE v=@value
如何能从一个字串中查询结果集呢？错误的把字串当成表对象来理解.
请记住@table是个表名,它是个字段，而非表对象,不是object
可以用EXEC执行动态语句来解决，比如
-
SQL code
-
EXEC ( ' SELECT * FROM [ ' + @table + ' ] WHERE v= ''' + @value + '''' )
(2)为什么我在执行一个批语句(可能是存储过程，
也可能是个FUNCTION,也可能只是几条语句的组合)时，
提示错误，我照着提示的错误，检查，但是那里没有报错啊
--=**********************************************
比如，上面的第二个存储过程,@table明明是存储过程的输入参数，它为什么提示我@table未定义
在上面，我已经讲了这句为什么出错的原因，
当然@table如果是表变量的话，那么那句select是不会有问题的，
但表变量不能用做输入参数。但它为什么这样提示呢?
这与sql内部机制有关,sql查询语句执行前先由命令解析器进行语法检查，如果语法检查未通过，
会扔出错误信息（通常这里的提示是精确的），
当语法检查通过，则将其编译为可执行的内部格式(查询树),
而非语法错误时，因为是执行时报错,执行期间是内部格式代码，只能扔出个大致错误信息.
了解了这一点，当您的sql语句报错后，先检查是否语法错误，
如果不是，那么需要仔细检查了，因为按着错误提示去找，很有可能兜圈子。
--=**********************************************
(3)为什么我明明定义了@n变量，却提示我变量不存在?
-
SQL code
-
-- =**********************************************
DECLARE @field VARCHAR ( 10 ), @n INT
SET @field = ' v '
EXEC ( ' SELECT @n=COUNT(*) FROM tb WHERE [ ' + @field + ' ]= '' a ''' )
上面有三条语句:
rows 语句
1 定义两变量
2 给@field赋值
3 执行动态语句
那么这三条语句，在执行时进行语法检查和编译,大至为:
-
SQL code
-
DECLARE
SET
EXEC
这是编译后要执行三步操作，它们在一进程空间中进行编译。
随着语句的运行，exec内部的语句被购造成'select @n=count(*) from tb where v=''a'''
这时，EXEC要执行的这条语句，继续要被命令解析器进行检查和编译,
它的编译内存空间与外部这个批不同，术语我可能表达不太清楚，
只想说明一点，动态语句在执行过程中才被首次编译,所以在这个空间中，提示@n未定义.
解决方式，sqlserver为我们提供了sp_executesql来完成这个操作。
上面要完成的操作，可以改写为:
-
SQL code
-
DECLARE @field VARCHAR ( 10 ), @n INT , @sql NVARCHAR ( 4000 )
SET @field = ' v '
SET @sql = N ' SELECT @n=COUNT(*) FROM tb WHERE [ ' + @field + ' ]= '' a '''
EXEC sp_executeSQL @sql ,N ' @n INT OUTPUT ' , @n OUTPUT
SELECT @n
-- =**********************************************
(4)以下语句为什么提示我临时表不存在，明明我生成了临时表的
--=**********************************************
-
SQL code
-
EXEC ( ' SELECT * INTO # FROM tb ' )
SELECT * FROM #
说明:
在上面解释@n为何未定义时说了关于编译时进程内空间的问题。
我们再联合局部临时表的生名周期，局部临时表在当间会话进程中有效
EXEC内部的语句是新开辟的一个进程空间执行的，所以当它执行完毕后，
生命周期结束，因此外部无法再访问
那为什么下面的又可以呢?
-
SQL code
-
SELECT * INTO # FROM tb
EXEC ( ' SELECT * FROM # ' )
PRINT ' aa '
DROP TABLE #
-- =********************************************
这个很明显,上面执行了三行，在一个批中,直到PRINT 'aa'结束后，
进程结束，#临时表才会被销毁，因为它的生命周期在整个会话中.
所以当EXEC执行时它仍存在
(5)为什么我给一个变量赋值，得到的不是我想要的,即为什么@v的值不是a,而是z
-
SQL code
-
-- =**********************************************
DECLARE @v VARCHAR ( 10 )
SELECT @v = v FROM tb
SELECT @v
说明
SELECT 变量=字段 FROM tb
这种赋值与SET赋值的主要区别:
a, SET赋值，一次只能给一个变量赋值，而SELECT 则可多个
b, SET赋值语义更明确，是赋值。而SELECT 可能是赋值也可能是数据查询
c, 最重要的一个,SELECT 可以从表中取值，而SET不能。
说到这有人会说 SET @v=(SELECT TOP 1 v FROM tb) 也可以,
这样确实可以，但实际上它还是调用SELECT来完成.
d, SELECT赋值时是滚动赋值(或许用词不科学），我来说明一下我的'滚动赋值'指的什么
即，当SELECT v FROM tb有多个结果时，
SELECT @v=v FROM tb 在产生结果集的过程中，每得到一条记录，
@v都被赋一次值，也就是说，语句会滚动结果集，每次都对@v赋值。
这样，也就产生了递规查询变量:"
-
SQL code
-
SELECT @v = @v + v FROM tb
这样就解释了为什么您的语句得到的结果为z而不是a
try:
-
SQL code
-
SELECT TOP 3 @v = v FROM tb -- 滚动到结果集的第三行,或者说，结果集中只有三行，取最后一行
-- =**********************************************
(6)我要获得tb上新插入记录的标识值，为什么我的@@identity全局函数取的值不对?
-
SQL code
-
-- =**********************************************
IF object_id ( ' ta ' ) IS NOT NULL
DROP TABLE ta
CREATE TABLE ta(id INT IDENTITY ( 1 , 1 ),b_id INT ,logTime DATETIME NOT NULL DEFAULT GETDATE ())
GO
CREATE TRIGGER t ON tb
FOR INSERT
AS
INSERT ta (b_id) SELECT id FROM INSERTED
GO
INSERT tb SELECT ' dd '
SELECT @@identity -- 为什么这里得到的不是5,而是1?
说明:
@@identity是个全局函数，返回当前会话所有作用域最后产生的标识值(执行SELECT @@identity之前的最后)
我们可以看到ta上有insert触发器 t,触发器里对ta进行了insert，所以这里得到的是ta中新增的标识值
建议使用 SCOPE_IDENTITY
-
SQL code
-
INSERT tb SELECT ' ww '
SELECT SCOPE_IDENTITY () -- 这里得到6,因为它返回当前会话，当前作用域最后产生的标识值
-- =**********************************************
(7)为什么我执行distinct去重复后没有效果?如下，我想得到v值不同的记录，也即结果集中每个v值只出现一次
-
SQL code
-
-- =**********************************************
INSERT tb SELECT ' a '
UNION ALL SELECT ' b '
SELECT * FROM tb -- 8 rows
SELECT DISTINCT v,id FROM tb -- 8 rows
-- =**********************************************
出现这个意外，您需要注意两点:
1,DISTINCT 有效范围是后面的字段列表,而非紧告其的第一个字段
2,为什么DISTINCT 会有这么个奇怪的特点？这与二维关系统有关
就说tb中v=a的记录id有1,7
DISTINCT v,id 如果仅对v有效，那么引id有1,7,在一行一列中如何填充数据？是用1还是用7?
您太难为它了，因为它不知道如何为您将id填充到结果集中.
其实这个问题就是常见的同组取一条，那么你需要指定多一个条件，
每组(v相同的记录为一组)取id最大的或最小的，那么这样的语句，写的很多，论坛上问的也很多了，我就不再累赘了。
5384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
