Post reply

stored procedure within stored procedure

  • July 4, 2006 at 2:49 pm

    #168824

    can u use another stored procedure in one procedure i.e. stored procedure within stored procedure?

    if yes then plz give example

  • July 4, 2006 at 2:51 pm

    #647388

    surely

    CREATE PROCEDURE dbo.USP_BATCH

    AS

    EXECUTE dbo.USP_BATCH_START

    EXECUTE dbo.USP_BATCH_MIDDLE

    EXECUTE dbo.USP_BATCH_CLEANUP

     

  • April 17, 2007 at 5:15 pm

    #701047

    are stored procedures selectable?

    ie...

    create proc dbo.sp

    as

    select * from tableA where

    col in(   ( select col from (select exec spB)  )   )

     

  • April 18, 2007 at 8:26 am

    #701135

    Not directly. You can load the results returned by a stored procedure into a table, including temporary tables or table variables if necessary, which can then be used in a subquery.

    There is an example in BOL (2000) under the topic INSERT (described) titled "Load data using the SELECT and EXECUTE options" that shows how you can do this.

    --Andrew

  • April 18, 2007 at 10:50 am

    #701176

    solution:

    CREATE TABLE #t(v varchar(100))--columns&column metadata must match resultset of query

    INSERT

    #t EXEC dbo.sp

    select

    * from tableA where col in (select col from #t)

    DROP

    TABLE #t

  • April 19, 2007 at 8:10 am

    #701442

    >You can load the results returned by a stored procedure into a table, including . . . table variables if necessary

     

    Actually this is incorrect.  You cannot do:

    INSERT @TabVar EXEC dbo.mysproc

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 19, 2007 at 9:52 am

    #701486

    what Guru is saying is you will receive an error if you try and use EXEC to populate a @table variable;you must use a #temp or ##temp table

    here's a proc calling a proc example:

    create proc pr_InnerTest As

    BEGIN

    SELECT TOP 5 NAME,ID FROM SYSOBJECTS WHERE XTYPE='U'

    END

    GO

    CREATEPROC PR_OUTERTEST AS

      BEGIN

        SET NOCOUNT ON

        CREATE  TABLE #TABVAR (OBJNAME VARCHAR(30),OBJID INT,EXTRACOLUMN INT)

        INSERT INTO #TABVAR(OBJNAME,OBJID)

          EXEC DBO.pr_InnerTest

        SELECT NAME AS COLNAME,#TABVAR.*  FROM SYSCOLUMNS

          INNER JOIN #TABVAR ON SYSCOLUMNS.ID=#TABVAR.OBJID

      END

    GO

    --this returns the error

    --EXECUTE cannot be used as a source when inserting into a table variable.

    CREATE  PROC PR_OUTERTEST2 AS

      BEGIN

        SET NOCOUNT ON

        DECLARE  @TABVAR TABLE(OBJNAME VARCHAR(30),OBJID INT,EXTRACOLUMN INT)

        INSERT INTO @TABVAR(OBJNAME,OBJID)

          EXEC DBO.pr_InnerTest

        SELECT NAMEAS COLNAME,@TABVAR.*  FROM SYSCOLUMNS

          INNER JOIN @TABVAR ON SYSCOLUMNS.ID=@TABVAR.OBJID

      END

    GO

    EXEC PR_OUTERTEST

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 19, 2007 at 10:29 am

    #701499

    Hmm I think this didn't compile because you replaced # with @

     

    try this:

    you can't define tables using '@', you must use '#'?

    create

     proc dbo.p1

    as

    select

    id from syscolumns

    go

    create

     proc p

    as

    CREATE

    TABLE #t(id varchar(100))--columns&column metadata must match resultset of query

    INSERT

    #t EXEC dbo.p1

    select

    * from sysobjects where id in (select id from #t)

    DROP

    TABLE #t

  • April 20, 2007 at 6:52 am

    #701702

    You're right. Temporary tables work; table variables do not. That's what I get for inferring without testing first.

    --Andrew

  • April 20, 2007 at 7:10 am

    #701710

    Table variables are stronger in 2005.  If you're using it, you're in luck!

    This works in SQL Server 2005:

    create

    proc spTestInsert @tlike sysname = '%'

    as
    begin

        select [name] tablename from sys.tables where [name] like @tlike

    end
     
    declare

    @t table (tblname sysname)

    insert

    into @t (tblname)

      exec spTestInsert 'prod%'

     
    select

    * from @t

    But in 2000, you get this error:

    Msg 197, Level 15, State 1, Procedure spTestInsert, Line 14

    EXECUTE cannot be used as a source when inserting into a table variable.

    Hope this helps...

     

    Carter Burleigh



    But boss, why must the urgent always take precedence over the important?

  • April 20, 2007 at 8:01 am

    #701728

    I use it a lot to execute multiple sps from 1 click

    CREATE  PROCEDURE D

    @DTE datetime,

    @ID varchar(6)

    AS 

     execute A  @DTE,@ID

     execute B  @DTE,@ID

     execute C  @DTE,@ID

  • June 13, 2007 at 11:42 am

    #712699

    is there any way to make a stored procedure within the scope of a stored procedure? ie ,

    create/alter proc _

    as

    create/alter proc #_

    as

    exec #_

     

     

  • June 13, 2007 at 11:51 am

    #712703

    That's cool... do you know if table variables still have the same fault of not being able to use statisics and not be alterable once formed?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply