December 12, 2019 at 7:23 am
I need to send a temp table as parameter from one store procedure to other.
If it is not possible, I need to execute a procedure inside if statement as iteration basis.
please suggest me with syntax or with any examples?
December 12, 2019 at 9:37 am
No, you can't use a table variable as an output parameter. What you can do, though, is have the stored procedure return a result set, insert that into a table variable, and use that table variable as an argument for a table-valued parameter to the second stored procedure. Please try that and post back if there's anything in particular that you don't understand.
John
December 12, 2019 at 9:55 am
Can you post with example?
December 12, 2019 at 10:27 am
You cannot pass a #Table as a param, but what you can do is declare a #Table in proc 1 and then call proc2 from proc 1, and it will have access to the #Table created in the parent table.
Below is SQL to show John's option as well as my option
CREATE PROCEDURE dbo.Proc1
AS
BEGIN
CREATE TABLE #Results2 (ID int IDENTITY(1,1) NOT NULL, val int NOT NULL, SourceProc varchar(20) NOT NULL);
CREATE TABLE #Results3 (ID int IDENTITY(1,1) NOT NULL, val int NOT NULL, SourceProc varchar(20) NOT NULL);
INSERT INTO #Results2 (val, SourceProc)
EXEC dbo.Proc2;
EXEC dbo.Proc3;
SELECT * FROM #Results2;
SELECT * FROM #Results3;
END;
GO
CREATE PROCEDURE dbo.Proc2
AS
BEGIN
SELECT val = 2, SourceProc = 'Proc2' UNION ALL
SELECT val = 4, SourceProc = 'Proc2' UNION ALL
SELECT val = 6, SourceProc = 'Proc2'
END;
GO
CREATE PROCEDURE dbo.Proc3
AS
BEGIN
INSERT INTO #Results3 (val, SourceProc)
VALUES ( 1, 'Proc3' )
, ( 3, 'Proc3' )
, ( 5, 'Proc3' );
END;
GO
EXEC dbo.Proc1;
December 12, 2019 at 1:02 pm
It's possible to pass a user defined table type to a stored procedure as a readonly parameter. This code defines a table type called test_type1. The procedure dbo.test_proc1 declares a variable of type test_type1 and inserts a few records. Test_proc1 passes the table to test_proc2 as a readonly parameter. Test_proc2 then selects all the rows. Here's the code (copy/paste/run)
drop type if exists dbo.test_type1
go
create type dbo.test_type1
as
table(
set_numint unique not null,
repetitionsint not null,
weight_lbsint not null);
go
drop proc if exists dbo.test_proc1;
go
create proc dbo.test_proc1
as
set nocount on;
declare
@example_typedbo.test_type1;
insert @example_type(set_num, repetitions, weight_lbs)
values (1, 10, 125),(2,10,150),(3,8,175);
exec dbo.test_proc2 @example_type;
go
drop proc if exists dbo.test_proc2;
go
create proc dbo.test_proc2
@input_tabledbo.test_type1 readonly
as
select * from @input_table;
go
exec dbo.test_proc1;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply