如何向存储过程 (SQL Server) 调用的存储过程提供参数值?

问题描述:

基于答案此处,我已经开始了一个测试 SP,我计划最终建立它以多次调用,具有更多的临时表和不同的单位"参数值.不过,@BegDate、@EndDate 和 @SortBy 参数将始终相同 - 由用户提供.

Based on the answer here, I've got a start on a test SP that I plan to eventually build up to call several times, with more temp tables and different values for the "Unit" parameter. The @BegDate, @EndDate, and @SortBy params will always be the same, though - those provided by the user.

这是我目前所拥有的:

IF OBJECT_ID ( 'testSP', 'P' ) IS NOT NULL   
    DROP PROCEDURE testSP;  
GO
CREATE PROC [dbo].[testSP]
    @BegDate datetime,
    @EndDate datetime,
    @SortBy varchar(20)
AS
BEGIN
    SELECT * FROM sys.databases
END
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "FirstUnit"; @BegDate = @BegDate;  @EndDate = @EndDate; @SortBy = @SortBy; ')

SELECT * FROM #MyTempTable

我的问题:这是将参数传递给现有存储过程 (SP2BCalled) 的正确方法:

My question: Is this the right way to pass params to the existing Stored Procedure (SP2BCalled):

'EXEC SP2BCalled @Unit = "Abuelos"; @BegDate = @BegDate;  @EndDate = @EndDate; @SortBy = @SortBy; ')

IOW,被调用的 SP 需要将 @BegDate、@EndDate 和 @SortBy 参数传递给它,因此新的 SP 会收集这些参数以进行传递,但是这样做的正确语法是什么?

IOW, the SP being called requires @BegDate, @EndDate, and @SortBy parameters to be passed to it, so the new SP gathers those to pass on, but what is the correct syntax for doing so?

我应该这样做吗:

CREATE PROC [dbo].[testSP]
    @BegDateLocal datetime,
    @EndDateLocal datetime,
    @SortByLocal varchar(20)

...然后像这样调用现有的 SP:

...and then call the existing SP like so:

SELECT * INTO #MyTempTable1 FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "FirstUnit"; @BegDate = @BegDateLocal;  @EndDate = @EndDateLocal; @SortBy = @SortByLocal; ')

SELECT * INTO #MyTempTable2 FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "SecondUnit"; @BegDate = @BegDateLocal;  @EndDate = @EndDateLocal; @SortBy = @SortByLocal; ')

SELECT * INTO #MyTempTable3 FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "ThirdUnit"; @BegDate = @BegDateLocal;  @EndDate = @EndDateLocal; @SortBy = @SortByLocal; ')

...?,或者怎么样?

...?, or how?

然后我需要联合所有临时表(#MyTempTable1、#MyTempTable2 和 #MyTempTable3)并将其作为最终结果返回.

I will then need to UNION all the temp tables (#MyTempTable1, #MyTempTable2, and #MyTempTable3) and return that as the final result.

Heinrich Smit 是对的;当我尝试运行 testSP 时,我得到:

Heinrich Smit is right; when I tried to run the testSP, I got:

配置选项显示高级选项"从 0 更改为 1.运行 RECONFIGURE 语句进行安装.配置选项Ad Hoc Distributed Queries"从 0 更改为 1.运行 RECONFIGURE 语句进行安装.链接服务器(空)"的 OLE DB 访问接口SQLNCLI11"返回消息登录超时已过期".链接服务器(null)"的 OLE DB 访问接口SQLNCLI11"返回消息与 SQL Server 建立连接时发生与网络相关或特定于实例的错误.找不到或无法访问服务器.检查实例名称是否正确以及 SQL Server 是否配置为允许远程连接.有关详细信息,请参阅 SQL Server 联机丛书.".

本地参数是否与要调用的过程的参数名称相同并不重要.所以你问的这两种方式没有区别.

It doesn't matter whether the local parameters are the same name as the parameters of the procedure to be called or not. So there is no difference between the two ways you are asking about doing it.

重要的是你不应该用分号分隔参数.你应该使用逗号.

What does matter is that you should not separate the parameters with semi-colons. You should use commas.

'EXEC SP2BCalled @Unit = ''FirstUnit'', @BegDate = @BegDateLocal,  @EndDate = @EndDateLocal, @SortBy = @SortByLocal, ')