Sql server:How to use Store Procedure inside a store procedure

Sql server:How to use Store Procedure inside a store procedure

        Hi,
                      In this article we will see how use store procedures inside a another store procedures. for your  better understanding we will see the following example.

Example::

create table #color and add some data into #color

   Create table #color (c_ID int ,COLOR varchar(100))

   Insert into #color (c_ID ,COLOR )values(1,'RED')
   Insert into #color (c_ID ,COLOR )values(2,'white')
   Insert into #color (c_ID ,COLOR )values(3,'blue')
   Insert into #color (c_ID ,COLOR )values(4,'green')

Create inner store procedure to select a data from a table.

      Create PROC INNER_PROC
           @ID   INT,
           @COMID   INT
     AS
    BEGIN
             SELECT * FROM DBO.COLOR WHERE ID=@ID AND COMID=@COMID
    END

Below is example of use Procedure inside a procedure.

    
    Create PROC OUTER_PROC
       @ID      INT,
       @COMID    INT
       AS
       BEGIN
               CREATE TABLE #TEMP(COMID INT,ID INT,NAMES VARCHAR(50),FRZ BIT)
               INSERT INTO #TEMP EXEC('INNER_PROC '+@ID+','+@COMID)
               SELECT NAMES FROM #TEMP
       END

       EXEC INNER_PROC 1,1
       EXEC OUTER_PROC 1,1