将选择结果作为存储过程的参数传递 [英] Pass select result as parameter of stored procedure
问题描述
我有一个带有以下参数的T-SQL存储过程
I have a T-SQL stored procedure with the following parameters
CREATE PROCEDURE [dbo].[SaveData]
-- Add the parameters for the stored procedure here
@UserID varchar(50),
@ServiceID varchar(50),
@param1 varchar(50),
@param2 varchar(50),
@endDate datetime
AS BEGIN
.
.
-- my code --
我想知道是否可以将select
的结果作为参数传递
I want know if it is possible to pass a result of select
as parameter:
exec SaveDate (SELECT player.UserID,player.ServiceID, 'no','no',GETDATE()
FROM player)
我尝试了类似的方法,但是它不起作用.
I tried something like this, but it does not work.
推荐答案
您在示例中编写的SELECT查询可能会带回多行(您的SELECT不具有WHERE子句或TOP(n)).如果您打算让过程使用一组表格"参数,那么从SQL Server 2008开始,您可以使用表值参数.
The SELECT query you wrote in your example would probably bring back multiple rows (your SELECT does not feature a WHERE clause or a TOP(n)). If your intention is to be able to have your procedure engage a "tabular" set of parameters, from SQL Server 2008, you are able to use table valued parameters.
这涉及到创建用户定义的表表,并且无疑将意味着调整存储过程内部的逻辑.
This involves creating a user defined table table and will almost undoubtedly mean adjusting the logic inside the stored procedure.
希望这会有所帮助:)
请参见 http://msdn.microsoft.com/zh-cn/library/bb510489(SQL.100).aspx 有关更多信息.
See http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspx for more information.
这篇关于将选择结果作为存储过程的参数传递的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!