CODEWITHSUNDEEP
sql-server-2008
mu_sa
mu_sa

Reputation: 2735

How to use a return value from a procedure in another procedure

I am new to databases and sql-server 2008. I have a procedure which looks something like

CREATE PROCEDURE P @myint as int
AS
BEGIN

CREATE TABLE #temp (Quantity smallint, Timing smallint)

INSERT INTO #temp

SELECT 
    Order.quantity as 'Quantity',
    Order.ValidUntil - Order.ValidFrom / X

FROM
    Order

WHERE 
    Order.id = 123

SELECT * FROM #temp

DROP TABLE #temp

END

Now the problem is in the above select statement in second column where i mentioned 'X'. For this X i should have a value after executing another procedure which returns a table and i want to use values from certain column of that table.

So, instead of X i want to write something like

create table #tmp (col1 nvarchar(512), col2 smalldatetime, col3 smalldatetime, col4 int, col5 float)
Insert into #tmp EXEC ProcedureHere 6, '20130101', '20131231', 0, 400
select col4 from #tmp

Upvotes: 0

Views: 67

Answers (1)

EProgrammerNotFound
EProgrammerNotFound

Reputation: 2461

In your procedure you must put the parameters with the output attribute, when you define the parameter as OUT/OUTPUT the value will be available after the procedure execution finish.

--first declare all variables with the same type as table #tmp fields     
--remember: It's a better design put the declare block in the top of the procedure
declare @p1 nvarchar(512), 
        @p2 smalldatetime, 
        @p3 smalldatetime,
        @p4 int,
        @p5 float

--create the table  
create table #tmp (col1 nvarchar(512), col2 smalldatetime, col3 smalldatetime, col4 int, col5 float)

--call the procedure
EXEC ProcedureHere @p1, @p2, @p3, @p4, @p5             

--insert data into temporary table
Insert into #tmp 
  select @p1, @p2, @p3, @p4, @p5

--read col4 
select col4 from #tmp 
--or 
select @p4

Procedure DDL:

if another parameters is required, you simply add then in the mark (*):
Create Procedure ProcedureHere(
        @p1 nvarchar(512) output, 
        @p2 smalldatetime output, 
        @p3 smalldatetime output,
        @p4 int output,
        @p5 float output,
        *) as
begin
  .
  DoStuff
  .
  --define @p1 result value
  select @p1 = select something from somewhere
  --so on for the others parameters      
  .
end
go

Upvotes: 1

Related Questions