How can I provide parameter values to a Stored Proc called by a Stored Proc?

Question

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.

This is what I have so far:

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

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, 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?

Should I do it like this instead:

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

...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?

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

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

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install. OLE DB provider "SQLNCLI11" for linked server " (null)" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server " (null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

Answer 1

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, ')