select * from (EXEC sp_SomeStoredProc)
If you can't do this then what is stopping it from being added to the SQL standard or T-SQL?
如果你不能这样做那么什么阻止它被添加到SQL标准或T-SQL?
5 个解决方案
#1
You can't do this, however you can do it as an insert. e.g.
你不能这样做,但你可以作为插入。例如
insert mytable
exec myStoredProcedure
Also, never name your stored procedures sp_xxxx. This is because SQL will always search in the system stored procedure area due to the sp_ before looking in the user stored procedures, leading to a small loss in performance that could add it to be fairly significant on a process that is run frequently.
此外,永远不要命名您的存储过程sp_xxxx。这是因为在查看用户存储过程之前,SQL将始终在系统存储过程区域中搜索sp_,从而导致性能的轻微损失,这可能会使其在经常运行的进程上相当重要。
#2
It's possible, but certainly not the right way to go:
这是可能的,但肯定不是正确的方法:
USE test
GO
CREATE procedure dbo.select1 AS
SELECT 1
GO
EXEC sp_addlinkedserver @server='OQtest', @datasrc='localhost', @provider='SQLNCLI', @srvproduct=''
GO
SELECT * FROM OPENQUERY(OQtest, 'test.dbo.select1')
You may also need to adjust security settings on the server for this to work.
您可能还需要调整服务器上的安全设置才能使其正常工作。
#3
What if the stored proc returns no rows? Multiple result sets? Changes? The potential usages of a stored proc are many and varied.
如果存储过程没有返回任何行怎么办?多个结果集?变化?存储过程的潜在用法是多种多样的。
When you have SELECT * FROM TableOrView
, there is a direct binding and easily checked syntax and structure.
当你有SELECT * FROM TableOrView时,有一个直接绑定和易于检查的语法和结构。
More correctly, in the relational sense, a stored proc is not a relation/table so you can't select from it.
更准确地说,在关系意义上,存储过程不是关系/表,因此您无法从中进行选择。
User defined functions achieve what you want but allow the code to conform to some relation/table concept.
用户定义的函数实现了您想要的功能,但允许代码符合某些关系/表概念。
#4
You can't do it, but you could consider a function in sqlserver2005. Here's an example function that creates a table from a comma separated list
你不能这样做,但你可以考虑sqlserver2005中的一个函数。这是一个从逗号分隔列表创建表的示例函数
Create Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end
And then simple select from the function...
然后从功能中简单选择......
Select * FROM dbo.CsvToInt('1,2,3,5')
And you'll get a table value.
你会得到一个表值。
#5
You can use the approach described by ck but this is not really recommended. You can check the INSERT-EXEC section of a great post How to Share Data Between Stored Procedures by Erland Sommarskog for more details.
您可以使用ck描述的方法,但这不是真正推荐的。您可以查看Erland Sommarskog如何在存储过程之间共享数据的INSERT-EXEC部分以获取更多详细信息。
#1
You can't do this, however you can do it as an insert. e.g.
你不能这样做,但你可以作为插入。例如
insert mytable
exec myStoredProcedure
Also, never name your stored procedures sp_xxxx. This is because SQL will always search in the system stored procedure area due to the sp_ before looking in the user stored procedures, leading to a small loss in performance that could add it to be fairly significant on a process that is run frequently.
此外,永远不要命名您的存储过程sp_xxxx。这是因为在查看用户存储过程之前,SQL将始终在系统存储过程区域中搜索sp_,从而导致性能的轻微损失,这可能会使其在经常运行的进程上相当重要。
#2
It's possible, but certainly not the right way to go:
这是可能的,但肯定不是正确的方法:
USE test
GO
CREATE procedure dbo.select1 AS
SELECT 1
GO
EXEC sp_addlinkedserver @server='OQtest', @datasrc='localhost', @provider='SQLNCLI', @srvproduct=''
GO
SELECT * FROM OPENQUERY(OQtest, 'test.dbo.select1')
You may also need to adjust security settings on the server for this to work.
您可能还需要调整服务器上的安全设置才能使其正常工作。
#3
What if the stored proc returns no rows? Multiple result sets? Changes? The potential usages of a stored proc are many and varied.
如果存储过程没有返回任何行怎么办?多个结果集?变化?存储过程的潜在用法是多种多样的。
When you have SELECT * FROM TableOrView
, there is a direct binding and easily checked syntax and structure.
当你有SELECT * FROM TableOrView时,有一个直接绑定和易于检查的语法和结构。
More correctly, in the relational sense, a stored proc is not a relation/table so you can't select from it.
更准确地说,在关系意义上,存储过程不是关系/表,因此您无法从中进行选择。
User defined functions achieve what you want but allow the code to conform to some relation/table concept.
用户定义的函数实现了您想要的功能,但允许代码符合某些关系/表概念。
#4
You can't do it, but you could consider a function in sqlserver2005. Here's an example function that creates a table from a comma separated list
你不能这样做,但你可以考虑sqlserver2005中的一个函数。这是一个从逗号分隔列表创建表的示例函数
Create Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end
And then simple select from the function...
然后从功能中简单选择......
Select * FROM dbo.CsvToInt('1,2,3,5')
And you'll get a table value.
你会得到一个表值。
#5
You can use the approach described by ck but this is not really recommended. You can check the INSERT-EXEC section of a great post How to Share Data Between Stored Procedures by Erland Sommarskog for more details.
您可以使用ck描述的方法,但这不是真正推荐的。您可以查看Erland Sommarskog如何在存储过程之间共享数据的INSERT-EXEC部分以获取更多详细信息。