I have an article on my web site, How to Share Data Between Stored Procedures which discusses the various options.
How to get data from two tables from sub procedure to main procedure without using insert into?
I work on sql server 2014 I can't get data from sub procedure to main procedure
sub procedure will select data from two tables
and after two tables select and get data
i need to get data from two tables on main report without using insert into
because i can't create new physical table to get data ?
so how to do it
my sample as below
create Proc [Parts].[sp_TradeCodeGenerateByPLandCodeType]
AS
BEGIN
IF OBJECT_ID('Extractreports.dbo.PartGeneration') IS NOT NULL
DROP TABLE Extractreports.dbo.PartGeneration
CREATE TABLE Extractreports.dbo.PartGeneration
(
ID INT IDENTITY(1, 1) ,
ZProductID INT ,
Proceed INT,
[Status] VARCHAR(200)
)
insert into Extractreports.dbo.PartGeneration
(
ZProductID
)
Select
4125
union all
select 4123
union all
select 45911
DECLARE @ZProductID INT =NULL
While (Select Count(1) From Extractreports.dbo.PartGeneration where Proceed =0 AND [Status] IS NULL ) > 0
BEGIN
BEGIN TRY
SELECT TOP 1 @ZProductID = ZProductID
FROM Extractreports.dbo.PartGeneration WHERE [Status] IS NULL AND Proceed=0
EXEC [dbo].[SP_TradeCodeGenerateByProductAndCodeType] @ZProductID
UPDATE Extractreports.dbo.PartGeneration Set Proceed = 1,Status='Done' Where @ZProductID=ZProductID
END TRY
BEGIN CATCH
UPDATE Extractreports.dbo.PartGeneration Set Proceed = 1,Status= CONCAT('Failied:',ERROR_MESSAGE()) Where @ZProductID=ZProductID
END CATCH
END
ALTER PROC [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
(
@productID INT
)
AS
BEGIN
select * from trades where zplid=@productID
select * from codesData where zplid=@productID
end
Now i need to get data from tables trades and table codesData on
from sub procedure [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
to
main procedure [Parts].[sp_TradeCodeGenerateByPLandCodeType]
so How t do that without using insert into
How to pass data from sub procedure [dbo].[SP_TradeCodeGenerateByProductAndCodeType]
to main procedure
[Parts].[sp_TradeCodeGenerateByPLandCodeType]
so i can get dat result of two tables select on main procedure
[Parts].[sp_TradeCodeGenerateByPLandCodeType]
2 answers
Sort by: Most helpful
-
-
EchoLiu-MSFT 14,601 Reputation points
2021-11-11T08:51:37.613+00:00 Hi @ahmed salah ,
I can't find the statement to call the table from the subroutine in your main program [Parts].[sp_TradeCodeGenerateByPLandCodeType].
Please refer to a simple example first:
CREATE TABLE [dbo].[MyTable]( [col1] [int] NOT NULL, [col2] [char](5) NULL ) ON [PRIMARY] INSERT INTO [MyTable] ([col1], [col2]) VALUES (1, A) Go INSERT INTO [MyTable] ([col1], [col2]) VALUES (2, B) GO INSERT INTO [MyTable] ([col1], [col2]) VALUES (3, C) Go INSERT INTO [MyTable] ([col1], [col2]) VALUES (4, D) Go -- If Exist then drop and create IF EXISTS (SELECT * FROM sys.objects WHERE object_id = Object_id(N'[dbo].[called_procedure]') AND type IN ( N'P', N'PC' )) DROP PROCEDURE [dbo].[called_procedure] GO CREATE PROCEDURE Called_procedure @par1 INT, @par2 BIT AS BEGIN IF Object_id('tempdb..#mytemp') IS NOT NULL BEGIN INSERT INTO #mytemp SELECT * FROM Mytable WHERE col1 = @par1 END END Go IF EXISTS (SELECT * FROM sys.objects WHERE object_id = Object_id(N'[dbo].[caller_procedure1]') AND type IN ( N'P', N'PC' )) DROP PROCEDURE [dbo].[caller_procedure1] GO CREATE PROCEDURE Caller_procedure1 AS BEGIN --Testing for if temp table does not exists in scope EXEC Called_procedure 1, 0 IF Object_id('tempdb..#mytemp') IS NOT NULL BEGIN SELECT * FROM #mytemp END END GO
Regards,
Echo