在存储过程中参数化FROM子句：如何？ [英] Parameterize FROM clause in Stored Procedure: How To?
问题描述
我需要编写一个从表中选择的存储过程，并且
返回结果集。我并不总是知道在运行时使表格合格时我需要使用的TableSchema
示例：正确的表可能是dbo.MyTable或
zzz.MyTable。
我希望用户输入架构的名称作为
$ b的参数运行时的$ b过程。
但是，当我尝试使用FROM子句中的参数创建过程
时，SQL Server会出错。
谁能帮帮忙？
谢谢，
比尔
运行下面的脚本来查看我的问题：
/ *制作样本表* /
创建表MyTable（TabKey int NOT NULL）;
alter table MyTable add constraint MyTable_PK主键（TabKey）;
/ *插入样本值* /
插入MyTable值（1）;
插入MyTable值（2）;
插入MyTable值（3）;
/ * Thi声明工作正常。 （注意，我没有使用@TableSchema
只是声明它证明有
声明中没有语法错误* /
CREATE PROCEDURE TestProc（@TableSchema varchar（80））AS
BEGIN
SELECT * from dbo.MyTable
END;
/ *运行程序（无论我为参数添加的内容）* /
TestProc''dbo''
/ *删除程序* /
drop procedure TestProc
/ *尝试使用参数化FROM子句重新创建过程
使用@TableSchema，但收到错误* /
CREATE PROCEDURE TestProc（@TableSchema varchar（80））AS
BEGIN
来自@ TableSchema.MyTable的SELECT * ----<<<<这会导致
错误
END
/ *
以下是错误消息：
消息156，级别15，状态1，过程TestProc，第5行
关键字附近的语法不正确'结束''。
* /
I need to write a stored procedure that selects from a table and
returns the result set. I don''t always know the TableSchema that I
need to use when qualifying the table at run-time
Example: The correct table could either be dbo.MyTable or
zzz.MyTable.
I want the user to enter the name of the schema as a parameter of the
procedure at run-time.
However, SQL Server gives me an error when I try create the procedure
using the parameter in the FROM clause.
Can anyone give me a hand?
Thanks,
Bill
Run the scripts below to see my problem:
/* Make the sample table */
create table MyTable (TabKey int NOT NULL);
alter table MyTable add constraint MyTable_PK primary key (TabKey);
/* insert sample values */
insert into MyTable values (1);
insert into MyTable values (2);
insert into MyTable values (3);
/*This statement works fine. (Notice, I don''t make use of @TableSchema
just declare it to prove there is
no syntax error in the declaration */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from dbo.MyTable
END;
/* Run the Procedure (Doesn''t matter what I put for the parameter) */
TestProc ''dbo''
/* Drop Procedure */
drop procedure TestProc
/* Try to Re-create the procedure with a parameterized FROM clause
that uses @TableSchema, but get an error */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from @TableSchema.MyTable ---- <<<< This causes the
error
END
/*
Here is the error message:
Msg 156, Level 15, State 1, Procedure TestProc, Line 5
Incorrect syntax near the keyword ''END''.
*/
推荐答案
我希望用户输入模式的名称作为参数在运行时
I want the user to enter the name of the schema as a parameter of the
过程。
procedure at run-time.
这个要求是因为你有许多相同的表具有相同的
结构但数据不同？恕我直言，在这种情况下更好的方法可能是
添加分区列并使用单个表。有关动态SQL注意事项的详细讨论，请参阅Erland的文章
：
http://www.sommarskog.se/dynamic_sql.html
请注意，在生产中避免使用SELECT *是一种好习惯代码和
而是指定一个显式列列表。存储过程接口
（参数和结果集）应该是明确定义的，并且不依赖于那天恰好在表中的
列。
-
希望这会有所帮助。
Dan Guzman
SQL Server MVP http://weblogs.sqlteam.com/dang/
< bi ********** @ gmail.comwrote in message
news：91 ************ ********************** @ u27g2000 pro.googlegroups.com ...
Is this requirement because you have many identical tables with the same
structure but different data? IMHO, a better approach in this case might be
to add a partitioning column and use a single table. See Erland''s article
for a thorough discussion of dynamic SQL considerations:
http://www.sommarskog.se/dynamic_sql.html
Note that it''s a good practice to avoid SELECT * in production code and
instead specify an explicit column list. A stored procedure interface
(parameters and resultset) should be well-defined and not dependent on the
columns that happen to be in the table that day.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
<bi**********@gmail.comwrote in message
news:91**********************************@u27g2000 pro.googlegroups.com...
>我需要编写一个从表中选择的存储过程，并且
返回结果集。我并不总是知道在运行时使表格合格时我需要使用的TableSchema
示例：正确的表可能是dbo.MyTable或
zzz.MyTable。
我希望用户输入架构的名称作为
$ b的参数运行时的$ b过程。
但是，当我尝试使用FROM子句中的参数创建过程
时，SQL Server会出错。
谁能帮帮忙？
谢谢，
比尔
运行下面的脚本来查看我的问题：
/ *制作样本表* /
创建表MyTable（TabKey int NOT NULL）;
alter table MyTable add constraint MyTable_PK主键（TabKey）;
/ *插入样本值* /
插入MyTable值（1）;
插入MyTable值（2）;
插入MyTable值（3）;
/ *这位政治家工作正常。 （注意，我没有使用@TableSchema
只是声明它证明有
声明中没有语法错误* /
CREATE PROCEDURE TestProc（@TableSchema varchar（80））AS
BEGIN
SELECT * from dbo.MyTable
END;
/ *运行程序（无论我为参数添加的内容）* /
TestProc''dbo''
/ *删除程序* /
drop procedure TestProc
/ *尝试使用参数化FROM子句重新创建过程
使用@TableSchema，但收到错误* /
CREATE PROCEDURE TestProc（@TableSchema varchar（80））AS
BEGIN
来自@ TableSchema.MyTable的SELECT * ----<<<<这会导致
错误
END
/ *
以下是错误消息：
消息156，级别15，状态1，过程TestProc，第5行
关键字''附近的语法不正确结束''。
* /
>I need to write a stored procedure that selects from a table and
returns the result set. I don''t always know the TableSchema that I
need to use when qualifying the table at run-time
Example: The correct table could either be dbo.MyTable or
zzz.MyTable.
I want the user to enter the name of the schema as a parameter of the
procedure at run-time.
However, SQL Server gives me an error when I try create the procedure
using the parameter in the FROM clause.
Can anyone give me a hand?
Thanks,
Bill
Run the scripts below to see my problem:
/* Make the sample table */
create table MyTable (TabKey int NOT NULL);
alter table MyTable add constraint MyTable_PK primary key (TabKey);
/* insert sample values */
insert into MyTable values (1);
insert into MyTable values (2);
insert into MyTable values (3);
/*This statement works fine. (Notice, I don''t make use of @TableSchema
just declare it to prove there is
no syntax error in the declaration */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from dbo.MyTable
END;
/* Run the Procedure (Doesn''t matter what I put for the parameter) */
TestProc ''dbo''
/* Drop Procedure */
drop procedure TestProc
/* Try to Re-create the procedure with a parameterized FROM clause
that uses @TableSchema, but get an error */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from @TableSchema.MyTable ---- <<<< This causes the
error
END
/*
Here is the error message:
Msg 156, Level 15, State 1, Procedure TestProc, Line 5
Incorrect syntax near the keyword ''END''.
*/
嗨丹，
RE：带分区列的单表。我同意这将是一个很好的解决方案，但我可能无法控制这个特殊情况。
RE：SELECT *。我同意并且永远不会在生产中使用它。我只是在示例脚本中使用它来实现打字的经济性。
这让我仍然需要创建一个带有
$ b的程序$ b参数化FROM子句。关于如何做到这一点的任何想法？
谢谢，
比尔
10月17日，3：34 * am，Dan Guzman， < guzma ... @nospam-
online.sbcglobal.netwrote：
Hi Dan,
RE: Single table with a partitioning column. I agree that this would
be a good solution, but it may not be possible for me to control this
particular situation.
RE: SELECT *. I agree and would never use it in production. I just
used it in the example scripts for economy of typing.
This leaves me still needing to create a procedure with a
parameterized FROM clause. Any ideas on how to do that?
Thanks,
Bill
On Oct 17, 3:34*am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.netwrote:
我想要用户在运行时输入模式的名称作为
过程的参数。
I want the user to enter the name of the schema as a parameter of the
procedure at run-time.
这个要求是因为你有许多相同的表格具有相同的
结构但数据不同？ *恕我直言，在这种情况下更好的方法可能是
添加分区列并使用单个表。 *有关动态SQL注意事项的详细讨论，请参阅Erland的文章
： http://www.sommarskog.se/dynamic_sql.html
请注意，在生产代码中避免使用SELECT *是一种很好的做法br />
而是指定显式列列表。 *存储过程接口
（参数和结果集）应该是明确定义的，并且不依赖于那天恰好在表中的
列。
-
希望这会有所帮助。
Dan Guzman
SQL Server MVPhttp：/ /weblogs.sqlteam.com/dang/
Is this requirement because you have many identical tables with the same
structure but different data? *IMHO, a better approach in this case might be
to add a partitioning column and use a single table. *See Erland''s article
for a thorough discussion of dynamic SQL considerations:http://www.sommarskog.se/dynamic_sql.html
Note that it''s a good practice to avoid SELECT * in production code and
instead specify an explicit column list. *A stored procedure interface
(parameters and resultset) should be well-defined and not dependent on the
columns that happen to be in the table that day.
--
Hope this helps.
Dan Guzman
SQL Server MVPhttp://weblogs.sqlteam.com/dang/
嗨丹，
我一直在想更多关于这一点，因为我把一个参数传递给FROM子句似乎有点俗气。
。事实上，我不会把我的程序转换成动态SQL，并且废弃了存储计划的优势吗？
所以这里是我的想法：我不能对多个表格做任何事情
在不同的模式中（这个问题超出了我的控制范围）。但是，我知道
知道哪些模式可能包含我需要的表格版本
查询
我可以只是在一个已知的模式中构建一个视图，联合我需要查询的所有可能的表（这些是小表）？我将
在每个标识
TableSchema的Unioned SELECT中放置一个文字。像这样：
创建视图废话为
选择''dbo''TableSchema，TabKey
来自dbo.Mytable
UNION ALL
选择''yyy''TableSchema，TabKey
来自yyy.Mytable
现在我有一个查询的视图，其中包含我的所有数据，以及一个
列，它告诉我们什么模式保存基表。我的程序可以
只需要做一个WHERE TableSchema = @TableSchema来查询正确的
表。考虑到我无法消除多个模式中的基表
，这让我接近理想的单表格，并且需要一个
分区列。
您怎么看？
谢谢，
比尔
Hi Dan,
I''ve been thinking more about this, because I it seems kind of cheesy
to pass a parameter into the FROM clause. In fact, wouldn''t doing
that essentially turn my procedure into dynamic SQL, and trash the
advantage of a stored plan?
So here''s my thought: I can''t do anything about the multiple tables
in different schemas (that issue is beyond my control). However, I do
know which schemas might contain a version of the table that I need to
query
Could I just build a view in a known schema that Unions all the
possible tables I need to query (these are small tables)? I would
put a literal in each of the Unioned SELECTs that identifies the
TableSchema. Like this:
CREATE VIEW crap as
SELECT ''dbo'' TableSchema, TabKey
FROM dbo.Mytable
UNION ALL
SELECT ''yyy'' TableSchema, TabKey
FROM yyy.Mytable
Now I have a single view to query that contains all my data, plus a
column that tells what schema holds the base table. My procedure can
just do a WHERE TableSchema = @TableSchema to query the correct
table. This gets me prettty close to your ideal single table with a
partioning column, considering that I cannot eliminate the base tables
in multiple schemas.
What do you think?
Thanks,
Bill
这篇关于在存储过程中参数化FROM子句：如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
