Yeo Meng Tat Yeo Meng Tat - 5 months ago 13
SQL Question

Calling stored procedure from other stored procedure

This is stored procedure #1:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp1]
@SMonth As Integer,
@SYear As Integer
AS
BEGIN
DECLARE @test varchar(255)
exec @test = dbo.sp2

SET NOCOUNT ON;

SELECT
CMS_ORGANIZATION.ORG_ACTOR_GUID,
CMS_ORG_SHARES_HISTORY.EFFECTIVE_DATE AS EventDate, @test,
'Changed to' AS EventType,
'Share Capital (Authorized)' AS TypeOfChange,
CMS_ORGANIZATION.REG_NAME
FROM
CMS_ORGANIZATION
INNER JOIN
CMS_ORG_SHARES ON CMS_ORGANIZATION.ORG_ACTOR_GUID = CMS_ORG_SHARES.ORG_ACTOR_GUID
INNER JOIN
CMS_ORG_SHARES_HISTORY ON CMS_ORG_SHARES.ORG_SHARES_PK = CMS_ORG_SHARES_HISTORY.ORG_SHARES_GUID
WHERE
MONTH(CMS_ORG_SHARES_HISTORY.EFFECTIVE_DATE) = @SMonth
AND YEAR(CMS_ORG_SHARES_HISTORY.EFFECTIVE_DATE) = @SYear
AND (RTRIM(LTRIM(CMS_ORGANIZATION.REG_NAME)) IN (SELECT ClientName FROM ListofClinets))
END


This is stored procedure #2:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp2]
AS
BEGIN
SET NOCOUNT ON;

SELECT
CMS_MASTER_SETUP.Description + '' + CONVERT(varchar(50),
CAST(CMS_ORG_SHARES.AUTHORIZED_CAP_AMOUNT AS money), 1) as Name
FROM
CMS_ORGANIZATION
INNER JOIN
CMS_ORG_SHARES ON CMS_ORGANIZATION.ORG_ACTOR_GUID = CMS_ORG_SHARES.ORG_ACTOR_GUID
INNER JOIN
CMS_ORG_SHARES_HISTORY ON CMS_ORG_SHARES.ORG_SHARES_PK = CMS_ORG_SHARES_HISTORY.ORG_SHARES_GUID
LEFT OUTER JOIN
CMS_MASTER_SETUP ON CMS_ORGANIZATION.CURRENCY_GUID = CMS_MASTER_SETUP.MASTER_SETUP_PK
END


The problem is here is I was not able to execute
@test
in stored procedure #1 by calling the stored procedure #2. When I execute sp1, I got a null values instead but when I execute the query of sp2 in sp1, I got a correct value. May I know what is the possible solution or similar examples which can solve the issue?

Answer

Your stored proc sp2 outputs the result of a SELECT, but like all stored procs, it returns an integer using the return statement. You don't have a return statement, so Sql Server generates one for you: return 0. The purpose of the return code is to give feedback on whether it ran as expected. By convention, a return code of 0 means no errors.

This shows the difference between the return code and the output of a stored proc. Create a temp table #output to capture the rows of the SELECT that the stored proc outputs.

DECLARE @return_code int

-- capture the output of the stored proc sp2 in a temp table
create table #output( column_data varchar(max) )
insert #output( column_data )
exec @return_code = dbo.sp2   -- returns 0 because you have no RETURN statement

-- extract column_data from #output into variable @test
-- if there is more than one row in #output, it will take the last one
DECLARE @test varchar(255)
select @test = column_data from #output
Comments