CODEWITHSUNDEEP
sqlsql-serversql-server-2008
arios
arios

Reputation: 185

SQL Pass Parameters from One stored proc to another

I have two SQL scripts: Script A and B. Script A retrieves information and uses the sp_executesql command to call stored procedure (Script B). I can successfully accomplish this.

However, now i would like to pass a value from Script A to Script B. I am unable to accomplish this. @nID is the parameter I would like to pass. I am using this link as a reference https://msdn.microsoft.com/en-IN/library/ms188001.aspx

My scripts are attached. For simplicity, I just made my parameter value equal to 5. So how do I pass a '5' from one script to another? Thanks for any help.

Script A

     BEGIN



 SET NOCOUNT ON;
     DECLARE @sp nVARCHAR(25) 
     DECLARE @nID nvarchar(25)  
     DECLARE @busID nvarchar(25)
     DECLARE @ParmDefinition nvarchar(500)
     SET @ParmDefinition = N'@nID nvarchar(50) output ';  

    set @sp = 'WMCOMM';
    set @busID = 5
    execute sp_executesql @sp, @ParmDefinition, @nID = @busID;
        END

Script B

ALTER PROCEDURE [dbo].[WMCM]


AS
BEGIN 

    SET nocount ON; 
    declare @numID  nvarchar(50) 
    insert into tester(numid)
    values(@numID)
END

Upvotes: 0

Views: 1585

Answers (2)

granadaCoder
granadaCoder

Reputation: 27874

I dumbed-down the stored procedure to only show and return.

But this does what you want:

alter PROCEDURE [dbo].[WMCM]
@nID nvarchar(25) --DECLARE PARAMETER HERE THEN YOU CAN USE IT IN THE SP
, @InAndOutValue nvarchar(50) OUTPUT

AS
BEGIN 

    SET nocount ON; 


    Select '[dbo].[WMCM] has access to :' as ProcName , @nID as TheId

    Select '[dbo].[WMCM] can use same variable as input and output.  InValue :' as ProcName , @InAndOutValue as TheId

    select @InAndOutValue= 'NowImAnOutValue'

END



GO





DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)

SET @SQL_String = N'
  EXEC [dbo].[WMCM] @nID = @nID_input, @InAndOutValue = @InAndOutValue_out OUTPUT
'

SET @Parameter_Definition = N'
  @nID_input nvarchar(25), 
  @InAndOutValue_out nvarchar(50) OUTPUT'

DECLARE @nID nvarchar(25)
DECLARE @InAndOutValue nvarchar(50)

SET @nID = '5'

SET @InAndOutValue = 'InAndOutVariable_JustInValue'

EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @nID_input = @nID, @InAndOutValue_out = @InAndOutValue OUTPUT

SELECT @InAndOutValue as IGotTheInAndOutValue

Upvotes: 1

pacreely
pacreely

Reputation: 1931

Stored procedure B requires an input parameter

ALTER PROCEDURE [dbo].[WMCM]
@nID nvarchar(25) --DECLARE PARAMETER HERE THEN YOU CAN USE IT IN THE SP
AS
BEGIN 

    SET nocount ON; 
    declare @numID  nvarchar(50) 
    insert into tester(numid)
    values(@numID)
END

Here's a working template of what you are trying to acheive.

--RUN ME FIRST
CREATE procedure dbo.B
@nID AS NVARCHAR(5)
AS
SELECT @nID

GO

--RUN ME SECOND

CREATE procedure dbo.A
AS

DECLARE @nID NVARCHAR(50) = 'abc'
DECLARE @sp NVARCHAR(50) = 'EXECUTE dbo.B @nID'

EXECUTE sp_executesql @sp,N'@nID NVARCHAR(50)',@nID = @nID;

GO

--RUN ME THIRD
EXEC dbo.a

Upvotes: 1

Related Questions