How to return from SQL stored procedure

CREATE PROCEDURE [dbo].[ProcA] 	
AS
BEGIN	
	SET NOCOUNT ON;    
	select 1 as 'id', 'success' as description
END

CREATE TABLE [dbo].[SampleData](
	[id] [int] NULL,
	[description] [varchar](50) NULL
)

CREATE PROCEDURE [dbo].[ProcB] 	
AS
BEGIN	
	SET NOCOUNT ON;
    Insert into SampleData exec ProcA
END

For more details refer Is it possible to pass TABLE as the output parameter in stored procedure[^]

Create SP and add below script

INSERT INTO #tab EXEC Procedure

and Select * from #tab

Hi Vivek, form my view inserting data directly from stored procedure output into temp tables is a bad practice. This is because, in this case, you should be aware that every time you update the output of your stored procedure (by adding or deleting new columns for example) you must update the structure of temp tables in all other procedures that are calling it. If not, an error will be thrown owing to different columns in temp table and stored procedure output.

So, I would recommend you, if possible, to convert the stored procedure into a table-valued function, and finally inserting data in other procedures making use of this function.

Finally, you can call the function by writing 'select * into #tempTable from function(params)' or by creating a table variable previously and then inserting data into it. In this last case, it's a good practice to specify all the columns in the code, both in 'insert' statement and 'select' statement, trying to avoid massive use of "*" operator.


Regards.

CREATE TABLE #TEMP( -- Create a temp table with same definition
	[id] [int],
	[description] [varchar](MAX) 
)
Insert into #TEMP   -- Inserting the data returned from Proc A
exec ProcA <parameters...>

SELECT * FROM #TEMP -- Read the data like this wherever you need. 
DROP TABLE #TEMP   -- you can drop at last

If you really don't want to create a temp table, because of its dynamic column definition. then should try below link which is not advisable .
Click here

you have to return the id and description?
beacause the best way is to return only the column ID.

DECLARE	@id int

EXEC	@id = [dbo].[sp_your_proc]

Obviously you have to use return clause in sp_your_proc.
let me know if it helps