动态评估 [英] Dynamic evaluation
问题描述
/ *目标：动态评估表格行
平台：sql 2000
* /
使用northwind;
声明@tbl sysname
设置@tbl =''客户''
EXEC（''选择计数（*）来自''+ @ tbl）
/ *
为什么以下动态评估会失败
如果EXEC（ ''从''+ @ tbl选择计数（*）= 0
PRINT''没有行''
它的变体，
IF存在（EXEC（''选择计数（*）来自''+ @ tbl））= 0
PRINT''没有行''
* /
我知道更多的睡眠吗:)
TIA。
/* goal: dynamic evaluation of table row
platform: sql 2000
*/
use northwind;
declare @tbl sysname
set @tbl = ''customers''
EXEC(''select count(*) from '' +@tbl)
/*
Why the following dynamic evaluation would fail
IF EXEC(''select count(*) from '' +@tbl) = 0
PRINT ''no rows''
Its variant,
IF exists (EXEC(''select count(*) from '' +@tbl)) = 0
PRINT ''no rows''
*/
Do I know more sleep :)
TIA.
推荐答案
>为什么以下动态评估会失败
> Why the following dynamic evaluation would fail
IF EXEC（''select count（*）from''+ @ tbl）= 0
IF EXEC(''select count(*) from '' +@tbl) = 0
因为EXEC是一个声明，而不是表达式。
您可以将EXEC的结果插入表中
INSERT INTO foo（table_name， row_count）
EXEC（''选择'''''+ @ tbl +''''''，来自''+ @ tbl的count（*））
并从那里检索值。
或者你可以使用sp_executesql传递输入和输出参数。
或者你可以从SYSINDEXES中检索rowcount（基于索引统计数据，因此
物理行数与
SYSINDEXES中更新的计数之间存在一些延迟）。
-
David Portas
SQL Server MVP
-
Because EXEC is a statement, not an expression.
You could insert the result of EXEC into a table
INSERT INTO foo (table_name, row_count)
EXEC(''select ''''''+@tbl+'''''',count(*) from '' +@tbl)
and retrieve the value from there.
Or you could use sp_executesql to pass input and output parameters.
Or you could retrieve the rowcount from SYSINDEXES (based on index stats so
there is some latency between the physical rowcount and the count updated in
SYSINDEXES).
--
David Portas
SQL Server MVP
--
2005年1月17日12:32:34 -0800，NickName写道：
On 17 Jan 2005 12:32:34 -0800, NickName wrote:
/ *目标：dyna表行的麦克风评价平台：sql 2000
* /
使用northwind;
声明@tbl sysname
设置@tbl = ''客户''
EXEC（''选择计数（*）来自''+ @ tbl）
/ *
为什么以下动态评估会失败
如果EXEC（''选择计数（*）来自''+ @ tbl）= 0
PRINT''没有行''
它的变体，
IF存在（EXEC（''选择计数（*）来自''+ @ tbl））= 0
PRINT''没有行''
* /
我知道更多睡觉:)
TIA。
/* goal: dynamic evaluation of table row
platform: sql 2000
*/
use northwind;
declare @tbl sysname
set @tbl = ''customers''
EXEC(''select count(*) from '' +@tbl)
/*
Why the following dynamic evaluation would fail
IF EXEC(''select count(*) from '' +@tbl) = 0
PRINT ''no rows''
Its variant,
IF exists (EXEC(''select count(*) from '' +@tbl)) = 0
PRINT ''no rows''
*/
Do I know more sleep :)
TIA.
你好尼克，
我认为最重要的问题是：为什么你不知道你的桌子名字
？在设计良好的
数据库中，很少需要动态SQL。如果您觉得必须使用动态SQL，那么至少要阅读
SQL注入的危险。如果有人将@tbl设置为''客户
放弃客户'怎么办？ exec会愉快地计算
customers表中的行数，然后删除它。阅读 www.sommarskog.se/dynamic_sql.html 。
您的查询效率也很低。使用COUNT（*）来查找是否有
零行意味着SQL Server将花费时间来计算30 GB表中的确切行数
，只发现它不是0.如果
你使用EXISTS，SQL Server将在找到
第一行后立即停止搜索。
最后，您的查询在语法上是错误的。 EXECUTE（）是一个声明，
不是表达式。括号之间的所有内容都在它自己的上下文中运行，所以它应该是一个完整的批处理。下面的代码会运行
- 但它仍然容易受到SQL注入攻击！
EXEC（''如果存在（SELECT * FROM''） + @tbl +''）PRINT''''没有行''''''
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
Hi Nick,
I think that the most important question is: why don''t you know the names
of your tables? Dynamic SQL is very rarely necessary in a well designed
database. If you feel you must use dynamic SQL, then at least read up on
the dangers of SQL injection. What if somebody sets @tbl to ''customers
drop customers''? The exec will happily count the number of rows in the
customers table, then drop it. Read www.sommarskog.se/dynamic_sql.html.
Your query is also rather inefficient. Using COUNT(*) to find if there are
zero rows means that SQL Server will just spend the time to calculate the
exact amount of rows in your 30 GB table, only to find that it''s not 0. If
you use EXISTS instead, SQL Server will stop searching as soon as the
first row is found.
Finally, your queries are syntactically wrong. EXECUTE() is a statement,
not an expression. And everything included between the parentheses runs in
it''s own context, so it should be a complete batch. The code below would
run - but it''s still susceptible to SQL injection attacks!
EXEC (''IF EXISTS (SELECT * FROM '' + @tbl + '') PRINT ''''no rows'''''')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
谢谢，David。第一种选择似乎可行;至于第二个，
你提到它似乎不准确。
Don
Thanks, David. The first option seems doable; as for the second one,
as you mentioned it does not seem to accurate.
Don
这篇关于动态评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
