How to pass a parameter to a stored procedure which in turn pass this parameter value calling another stored procedure?

Question

I am using MS SQL Server 2017. Suppose I have two stored procedures SPA and SPB. They both take a parameter (e.g. both are integer parameters).

Is there any way to call SPA with a parameter value which would be passed to SPB for execution? Please see example below and it does not work. But this illustrates the idea. I got an error

Must declare the scalar variable "@param1"

I want 100 to be the value for @paramID1 when I run

EXEC SPB @paramID1 = @param1

Please advise. Thanks a million.

CREATE PROCEDURE SPA 
    @param1 INT
AS
    INSERT INTO tbl 
        SELECT a.* 
        FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
                'EXEC SPB @paramID1=@param1') AS a
GO

-- Usage:

EXEC SPA @param1 = 100;

check out: https://stackoverflow.com/questions/30737743/passing-parameter-values-to-stored-procedure-when-calling-from-another-procedure — Brad, Jan 03 '20 at 20:48

tgralex · Answer 1 · 2020-01-03T21:00:17.783

2

Instead of using dynamic SQL use explicit syntax. Below is completely working script:

CREATE TABLE dbo.tbl (Name VARCHAR(10))
GO
CREATE PROCEDURE dbo.SPB (@param1 INT)
AS
BEGIN
    SELECT @param1
END;
GO
CREATE PROCEDURE dbo.SPA (@param1 INT)
AS
BEGIN
    INSERT INTO dbo.tbl (Name)
    EXEC dbo.SPB @param1 = @param1    
END;
GO
---------------usage---

EXEC dbo.SPA @param1=100;
EXEC dbo.SPA @param1=200;
EXEC dbo.SPA @param1=300;
SELECT * FROM dbo.tbl
GO

DROP TABLE dbo.tbl
DROP PROC dbo.SPA
DROP PROC dbo.SPB

with result:

Name
100
200
300

edited Jan 03 '20 at 21:00

answered Jan 03 '20 at 20:50

tgralex

794
4
14

I am not sure this solution can work for me. To clarify, my SPB is actually a R script and the last statement is the following. WITH RESULT SETS ((ID1 INT, ID2 INT, [time] date,forecast DECIMAL(10,4),lo95 DECIMAL(10,4), hi95 DECIMAL(10,4), runtime time)); GO – FCHLuk Jan 04 '20 at 01:45

score 1 · Answer 2 · answered Jan 03 '20 at 20:51

1

You could use dynamic sql, like this...

CREATE PROCEDURE SPA @param1 INT
AS
BEGIN
    DECLARE @sql nvarchar(max)
    SET @sql='INSERT INTO tbl SELECT a.* 
            FROM OPENROWSET(
                   ''SQLNCLI'',
                   ''Server=(local);Trusted_Connection=yes;'',
                   ''EXEC dbo.SPB @paramID1=' + convert(varchar(10),@param1) + ''')'

     Exec(@sql)
END
GO

EXEC SPA @param1 =100;
GO

answered Jan 03 '20 at 20:51

jfarleyx

775
1
5
9

This solution works. However I have trouble trying to figure out what are the 3 's to the left of the bracket. ''EXEC dbo.SPB @paramID1=' + convert(varchar(10),@param1) + ''')' – FCHLuk Jan 05 '20 at 03:20
The first is needed to start a new string after the "+". The next 2 are needed to ensure the sql string is surrounded by single quotes when the string is rendered, as required by OPENROWSET. Then, we need a closing paren to close the OPENROWSET function. Finally, we have a single ' to close the string. A single ' is used to escape another '. More details here: https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server – jfarleyx Jan 05 '20 at 16:42
But there is no new string after convert(varchar(10),@param1) ! – FCHLuk Jan 07 '20 at 04:31
@FCHLuk There is. You need to close the opening paren from "OPENROWSET(" and the "EXEC" statement must be wrapped in a single quote. – jfarleyx Jan 07 '20 at 12:20
So do you mean the new string is the '' (two single quotes) following the first ' ? – FCHLuk Jan 08 '20 at 05:13
Yes, along with the closing paren. – jfarleyx Jan 08 '20 at 11:13

How to pass a parameter to a stored procedure which in turn pass this parameter value calling another stored procedure?

2 Answers2