November 10, 2021 at 11:41 am
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]
November 10, 2021 at 12:00 pm
You may simply execute the sub-procedure from the main procedure using the following syntax.
EXEC <sub procedure name>
The main procedure will look somewhat similar as follow. Please note to supply the parameters as well to your sub-procedure. You may find the detailed syntax of the Stored Procedure here.
CREATE PROCEDURE <main procedure name>
AS
BEGIN
SET NOCOUNT ON;
EXEC <sub procedure name>
<Param name> = <param value>;
END
November 10, 2021 at 3:46 pm
i don't understand what you mean
i ask for way move data from stored procedure to another
so how to do that please
November 10, 2021 at 3:58 pm
You can insert the data into a user-defined table type variable , add a table-valued parameter (TVP) to the second procedure referencing that table type, and pass the variable from the first proc to the second procedure as the TVP. e.g.:
CREATE TYPE [dbo].[IDTable] AS TABLE(
[ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
CREATE PROCEDURE dbo.SecondProd
@ids dbo.IDTable READONLY
AS
BEGIN
SELECT * FROM @ids ids;
END
GO
CREATE PROCEDURE dbo.FirstProc
AS
BEGIN
DECLARE @ids dbo.IDTABLE;
INSERT INTO @ids (ID)
VALUES(1),(2),(3);
EXEC dbo.SecondProc;
END
GO
EXEC dbo.FirstProc
You can insert the data into a temp table and reference that data in other procedure IF you call the second procedure from the first.
You appear to already be inserting into trades & codesData tables. Are you trying to avoid those inserts? If not, why can't you just select from those tables?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply