Geek asks and answers

How to use a SELECT result in one stored procedure in another dropped procedure?

I have a stored procedure that computes several values ​​and SELECTtheirs:

CREATE PROCEDURE [dbo].[MyProc]
AS
   DECLARE @value1 int;
   DECLARE @value2 int;
   SET @value1 =...
   IF( @value1 IS NULL ) 
       RETURN 0;
   SET @value2 =...
   SELECT @value1 AS Value1, @value2 AS Value2;
RETURN 0;

I know that I can turn this into a table function, but I would prefer not to do this because of RETURNin the middle - sometimes there is simply nothing to return.

I want to call this stored procedure from another stored procedure and use the values ​​obtained by using SELECTin another procedure. How to do it?

+5
source share
2 answers

You can create a seductive one and paste both values ​​into it.

CREATE TABLE #Temp (value1 int, value2 int)

INSERT INTO #Temp (value1, value2)
EXEC [dbo].[MyProc]

If 1 is NULL, there will be no write to #Temp, in which case you will not need to return 0.

, 0, @value1 @value2 .

+2

:

CREATE PROCEDURE [dbo].[MyProc]
(
  @value1 int = null output,
  @value2 int = null output
)
AS

   SET @value1 =...
   IF( @value1 IS NULL ) 
       RETURN 0;
   SET @value2 =...
   SELECT @value1 = Value1, 
          @value2 = Value2;
RETURN 0;

:

declare @v1 int, 
        @v2 int

exec MyProc @v1 out, @v2 out

select @v1, @v2

,

create table #tmp
(
  val1 int null,
  val2 int null 
)

CREATE PROCEDURE [dbo].[MyProc]   
AS

   SET @value1 =...
   IF( @value1 IS NULL ) 
       RETURN 0;
   SET @value2 =...

   insert into #tmp
   SELECT Value1, Value2
   from tab

RETURN 0;

:

create table #tmp
(
  val1 int null,
  val2 int null 
)

exec MyProc 

select *
from #tmp

drop table #tmp
+2

More articles:


All Articles