Let's say I have 'myStoredProcedure' that takes in an Id as a parameter, and returns a table of information.
假设我有'myStoredProcedure',它将Id作为参数,并返回一个信息表。
Is it possible to write a SQL statement similar to this?
是否可以编写类似于此的SQL语句?
SELECT
MyColumn
FROM
Table-ify('myStoredProcedure ' + @MyId) AS [MyTable]
I get the feeling that it's not, but it would be very beneficial in a scenario I have with legacy code & linked server tables
我觉得它不是,但在遗留代码和链接服务器表的情况下,它会非常有用
Thanks!
4 个解决方案
#1
You can use a table value function in this way.
您可以通过这种方式使用表值函数。
Here is a few tricks...
这是一些技巧......
#2
No it is not - at least not in any official or documented way - unless you change your stored procedure to a TVF.
不,不是 - 至少不是以任何官方或文件方式 - 除非您将存储过程更改为TVF。
But however there are ways (read) hacks to do it. All of them basically involved a linked server and using OpenQuery - for example seehere. Do however note that it is quite fragile as you need to hardcode the name of the server - so it can be problematic if you have multiple sql server instances with different name.
但是,有一些方法(阅读)黑客可以做到这一点。所有这些都基本上涉及链接服务器并使用OpenQuery - 例如seehere。但请注意,它非常脆弱,因为您需要对服务器的名称进行硬编码 - 因此,如果您有多个具有不同名称的sql server实例,则可能会出现问题。
#3
Here is a pretty good summary of the ways of sharing data between stored procedures http://www.sommarskog.se/share_data.html.
以下是存储过程http://www.sommarskog.se/share_data.html之间共享数据的方法的一个很好的总结。
Basically it depends what you want to do. The most common ways are creating the temporary table prior to calling the stored procedure and having it fill it, or having one permanent table that the stored procedure dumps the data into which also contains the process id.
基本上这取决于你想做什么。最常见的方法是在调用存储过程之前创建临时表并使其填充,或者有一个永久表,存储过程将数据转储到其中,其中还包含进程ID。
Table Valued functions have been mentioned, but there are a number of restrictions when you create a function as opposed to a stored procedure, so they may or may not be right for you. The link provides a good guide to what is available.
已经提到了表值函数,但是在创建函数而不是存储过程时存在许多限制,因此它们可能适合您,也可能不适合您。该链接提供了可用内容的良好指南。
SQL Server 2005 and SQL Server 2008 change the options a bit. SQL Server 2005+ make working with XML much easier. So XML can be passed as an output variable and pretty easily "shredded" into a table using the XML functions nodes and value. I believe SQL 2008 allows table variables to be passed into stored procedures (although read only). Since you cited SQL 2000 the 2005+ enhancements don't apply to you, but I mentioned them for completeness.
SQL Server 2005和SQL Server 2008稍微更改了选项。 SQL Server 2005+使得使用XML变得更加容易。因此,XML可以作为输出变量传递,并且很容易使用XML函数节点和值“切碎”到表中。我相信SQL 2008允许将表变量传递到存储过程(尽管只读)。由于您引用了SQL 2000,因此2005+增强功能不适用于您,但我提到它们是为了完整性。
Most likely you'll go with a table valued function, or creating the temporary table prior to calling the stored procedure and then having it populate that.
很可能你会使用表值函数,或者在调用存储过程之前创建临时表,然后让它填充它。
#4
While working on the project, I used the following to insert the results of xp_readerrorlog (afaik, returns a table) into a temporary table created ahead of time.
在处理项目时,我使用以下内容将xp_readerrorlog(afaik,返回表)的结果插入到提前创建的临时表中。
INSERT INTO [tempdb].[dbo].[ErrorLogsTMP]
EXEC master.dbo.xp_readerrorlog
From the temporary table, select the columns you want.
从临时表中,选择所需的列。
#1
You can use a table value function in this way.
您可以通过这种方式使用表值函数。
Here is a few tricks...
这是一些技巧......
#2
No it is not - at least not in any official or documented way - unless you change your stored procedure to a TVF.
不,不是 - 至少不是以任何官方或文件方式 - 除非您将存储过程更改为TVF。
But however there are ways (read) hacks to do it. All of them basically involved a linked server and using OpenQuery - for example seehere. Do however note that it is quite fragile as you need to hardcode the name of the server - so it can be problematic if you have multiple sql server instances with different name.
但是,有一些方法(阅读)黑客可以做到这一点。所有这些都基本上涉及链接服务器并使用OpenQuery - 例如seehere。但请注意,它非常脆弱,因为您需要对服务器的名称进行硬编码 - 因此,如果您有多个具有不同名称的sql server实例,则可能会出现问题。
#3
Here is a pretty good summary of the ways of sharing data between stored procedures http://www.sommarskog.se/share_data.html.
以下是存储过程http://www.sommarskog.se/share_data.html之间共享数据的方法的一个很好的总结。
Basically it depends what you want to do. The most common ways are creating the temporary table prior to calling the stored procedure and having it fill it, or having one permanent table that the stored procedure dumps the data into which also contains the process id.
基本上这取决于你想做什么。最常见的方法是在调用存储过程之前创建临时表并使其填充,或者有一个永久表,存储过程将数据转储到其中,其中还包含进程ID。
Table Valued functions have been mentioned, but there are a number of restrictions when you create a function as opposed to a stored procedure, so they may or may not be right for you. The link provides a good guide to what is available.
已经提到了表值函数,但是在创建函数而不是存储过程时存在许多限制,因此它们可能适合您,也可能不适合您。该链接提供了可用内容的良好指南。
SQL Server 2005 and SQL Server 2008 change the options a bit. SQL Server 2005+ make working with XML much easier. So XML can be passed as an output variable and pretty easily "shredded" into a table using the XML functions nodes and value. I believe SQL 2008 allows table variables to be passed into stored procedures (although read only). Since you cited SQL 2000 the 2005+ enhancements don't apply to you, but I mentioned them for completeness.
SQL Server 2005和SQL Server 2008稍微更改了选项。 SQL Server 2005+使得使用XML变得更加容易。因此,XML可以作为输出变量传递,并且很容易使用XML函数节点和值“切碎”到表中。我相信SQL 2008允许将表变量传递到存储过程(尽管只读)。由于您引用了SQL 2000,因此2005+增强功能不适用于您,但我提到它们是为了完整性。
Most likely you'll go with a table valued function, or creating the temporary table prior to calling the stored procedure and then having it populate that.
很可能你会使用表值函数,或者在调用存储过程之前创建临时表,然后让它填充它。
#4
While working on the project, I used the following to insert the results of xp_readerrorlog (afaik, returns a table) into a temporary table created ahead of time.
在处理项目时,我使用以下内容将xp_readerrorlog(afaik,返回表)的结果插入到提前创建的临时表中。
INSERT INTO [tempdb].[dbo].[ErrorLogsTMP]
EXEC master.dbo.xp_readerrorlog
From the temporary table, select the columns you want.
从临时表中,选择所需的列。