Can I EXECUTE a SQL Server Stored Procedure with Parameters and store the result set to a CTE Table so that I can then UNION to it

_{Monday, January 7, 2019 3:32 PM}

I have a requirement where I need to EXECUTE an existing SQL Server Stored Procedure using Parameters and then append to that result set list with a UNION Statement t

--
DECLARE

    @Parameter1     VARCHAR(MAX)    =   NULL,
    @Parameter2     SMALLINT    =   NULL
;
--
    SET @Parameter1 =   '';
    SET @Parameter2 =   1;

--
;
WITH    CTE_Name
AS      (
        EXECUTE [SchemaName].[SPROCName]    @Parameter1     =       @Parameter1,
                            @Parameter2     =       @Parameter2
        )
,
SELECT  *
FROM    [CTE_Name]
UNION ALL
SELECT  [TableName].[Column1],
    [TableName].[Column2]
FROM    [Schema].[TableName]
WHERE   [Schema].[TableName].[Column]       =       'SomeValue'
;

Thanks for your review and am hopeful for a reply.

o properly build my Report Parameter.

I tried the following T-SQL in vain...but does not seem to be correct...

_{Monday, January 7, 2019 3:58 PM}

One way you can use openquery to export your stroed procedure result to a temp table and use the temp table to UNION ALL your another table..

Here is an example:

SELECT  *
INTO     mytmpTable
FROM    OPENQUERY("myserver\Instancename", 'EXEC mydbname.[dbo].[usp_name_here] 2') -- with parameter value 2
  
select * from mytmpTable

_{Monday, January 7, 2019 11:13 PM}

You can't run a stored procedure from inside a query. The OPENQUERY that Jingyang posted has lots of problems and don't go there.

The article How to Share Data between Stored Procedures on my web site discusses various options, including OPENQUERY and why it is problematic.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Tuesday, January 8, 2019 3:35 AM | 1 vote}

Hi ITBobbyP,

Please try to use a temp table to achieve your requirement. And I will provide a simple example  and change your original script for you. Please try following script.

CREATE PROCEDURE MyStoredProc
@Parameter1 VARCHAR(MAX), 
@Parameter2 SMALLINT
as
begin
select @Parameter1,@Parameter2 
end 

go
DECLARE  @Parameter1 VARCHAR(MAX)=NULL
DECLARE  @Parameter2 SMALLINT=NULL
SET @Parameter1 =''
SET @Parameter2 =1
Declare @table1 table(col1 VARCHAR(MAX), col2 SMALLINT)
insert into @table1 EXECUTE MyStoredProc @Parameter1=@Parameter1,@Parameter2=@Parameter2

SELECT * FROM @table1
/*
col1       col2
 
           1
*/


your script
go
--
DECLARE @Parameter1     VARCHAR(MAX)    =   NULL,
    @Parameter2     SMALLINT    =   NULL;
--
    SET @Parameter1 =   '';
    SET @Parameter2 =   1;
--
Declare @table1 table(col1 datatype, col2 datatype,...)
insert into @table1(col1,col2...)
EXECUTE [SchemaName].[SPROCName] @Parameter1=@Parameter1,@Parameter2=@Parameter2

SELECT  * FROM  @table1
UNION ALL
SELECT  [TableName].[Column1],
    [TableName].[Column2]
FROM    [Schema].[TableName]
WHERE   [Schema].[TableName].[Column]='SomeValue'

Hope it can help you.

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.