CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Suraj
Suraj

Reputation: 675

Return results from stored proc to another stored proc

I have a stored proc - [dbo].[DynamicPivotTableInSql1] that I need to call from another stored proc and continue further query creation. I see that the stored proc [dbo].[DynamicPivotTableInSql1] is executing successfully but the results are not getting returned. Could you please let me know if I am doing something wrong here.

I have very recently posted a related question, but on further analysis found that the results not getting returned to the calling stored proc is the issue.

I need to use dynamic sql in [dbo].[DynamicPivotTableInSql1], because the query is quite complicated and I have to extend the where clause based on certain input parameters. I have only posted a part of it.

Thanks for your help and time!

CREATE PROCEDURE [dbo].[DynamicPivotTableInSql1]

AS
BEGIN
DECLARE @colsPivot AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX),
@str_comp1 AS NVARCHAR(MAX),
@str_comp2 AS NVARCHAR(MAX) 


SET @str_comp1 = ' comp1 IN (''Capacity'') and '
SET @str_comp2 = ' comp2 IN (''Refinery'') '

SET @colsPivot = 'SELECT STUFF((SELECT  '','' 
                      + quotename(link_id)
                    from [dbo].[test_excel_poc_head] t
WHERE ' + @str_comp1 +  @str_comp2 + '
FOR XML PATH(''''), TYPE ).value(''.'', ''NVARCHAR(MAX)'') 
,1,1,'''')'

EXEC(@colsPivot)
END

The calling procedure is below:

BEGIN
DECLARE @colsPivot1 AS NVARCHAR(MAX),
@return_result  AS NVARCHAR(MAX) 

EXEC    @return_result = [dbo].[DynamicPivotTableInSql1]
print('Return Value: ' + @return_result)
END

Below is the result of executing stored proc [dbo].[DynamicPivotTableInSql1]: enter image description here

But below is the returned result: enter image description here

Upvotes: 0

Views: 117

Answers (1)

critical_error
critical_error

Reputation: 6696

Your problem is that you're attempting to use a stored procedure as a function. A stored procedure returns a numeric value, 0 for success, or another value that represents an error.

Functions do not allow for calling things like EXEC, however, you can define a stored procedure's variable as OUT or OUTPUT (depending on the need) and in your case, it appears you only need OUT.

Here is an example of how you can do this:

/* Create base procedure with an OUT parameter */
CREATE OR ALTER PROCEDURE dbo.Procedure1 (
    @results xml OUT -- OUT parameter to return the results.
)
AS
BEGIN

    /* Build dynamic SQL statement */
    DECLARE @statement nvarchar(1000) = 'SET @dynamicResults = ( SELECT * FROM Misc FOR XML PATH( '''' ), TYPE );';

    /* Execute the dynamic SQL statement and capture results into the @results OUT parameter */
    EXEC sp_executesql @statement, N'@dynamicResults xml OUT', @dynamicResults = @results OUT;

END
GO

/* Create a procedure that captures the results from the base procedure */
CREATE OR ALTER PROCEDURE dbo.Procedure2
AS
BEGIN

    /* Declare variable to capture the results from Procedure1 */
    DECLARE @results xml;

    /* Call Procedure1 and capture its results */
    EXEC dbo.Procedure1 @results OUT;

    /* Return the results from Procedure1 */
    SELECT @results AS ResultsFromProcedure1;

END
GO

/* Call Procedure2 */
EXEC dbo.Procedure2;

Calling Procedure2 in this example simply returns the results captured from Procedure1. Once you have the results from the first procedure, you can continue working with them.

Upvotes: 1

Related Questions