Post reply

Defining local procedure

  • December 21, 2011 at 9:53 pm

    #262148

    Hi,

    Simple question about T-SQL:

    "Can I define a proceudre B in procedure A?"

    so that procedure B can only be called by the code in procedure A.

  • December 21, 2011 at 10:45 pm

    #1424570

    Can I define a proceudre B in procedure A?

    NO

    so that procedure B can only be called by the code in procedure A.

    YES

    For More: (Check for Best Practices & Restrictions)

    http://msdn.microsoft.com/en-us/library/ms187926.aspx

  • December 21, 2011 at 11:14 pm

    #1424583

    Actually, yes you can (sorry Dev). You can have one stored procedure create a temporary stored procedure that works just fine. See "Create Proceedure" in Books Online for more information. Just like Temp Tables begin with a # (local) or ## (global), so do Temporary Stored Procedures.

    The really cool part is, they disappear after the session that used them closes.

    --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

  • December 21, 2011 at 11:22 pm

    #1424586

    Now that you mention it, I do remember reading about temporary stored procedures. I wondered at the time (and I am wondering now) what possible use anyone could have for a temporary stored procedure.

    Could you also have temporary functions or temporary views? Are there any limitations on what you can create in the TempDB?

  • December 21, 2011 at 11:48 pm

    #1424594

    David Moutray (12/21/2011)

    Now that you mention it, I do remember reading about temporary stored procedures. I wondered at the time (and I am wondering now) what possible use anyone could have for a temporary stored procedure.

    Could you also have temporary functions or temporary views? Are there any limitations on what you can create in the TempDB?

    There's actually a Connect item that asks for MS to incorporate temporary views (which are not currently available and neither are temporary functions).

    In the old days, I used temporary stored procedures to beat the NVARCHAR(4000) limit of sp_ExecuteSQL. I don't use them so much anymore but they're handy when you have a sproc that will neve be used anywhere else because it reduces clutter. They'll also handy when you want the same stored procedure to run on more than one database or table without having to hardcode the database or tablename into the code.

    --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

  • December 22, 2011 at 12:29 am

    #1424600

    Jeff Moden (12/21/2011)

    Actually, yes you can (sorry Dev). You can have one stored procedure create a temporary stored procedure that works just fine. See "Create Proceedure" in Books Online for more information. Just like Temp Tables begin with a # (local) or ## (global), so do Temporary Stored Procedures.

    The really cool part is, they disappear after the session that used them closes.

    No Problem at all. It's news to me. :w00t:

    I read BOL & it explains about it but it doesn't have any example for Temporary Stored Procedures.

    Also, it contradicts the Restriction section (in same page) which explains 'CREATE or ALTER PROCEDURE' cannot be used anywhere in the body of a stored procedure. I can't connect to SQL Server now. Would you mind pulling one example for us?

    http://msdn.microsoft.com/en-us/library/ms187926.aspx

  • December 22, 2011 at 1:13 am

    #1424610

    One of my bad habits, if I need some info then I NEED that info. 😀

    Google-d it and found one snippet of Temporary Stored Procedure (sharing it here). But it’s not nested.

    SET NOCOUNT ON

    GO

    CREATE PROC #tempInsertProc

    @id integer

    AS

    INSERT INTO foo (bar) VALUES (@id)

    GO

    EXEC #tempInsertProc 10

    GO

    EXEC #tempInsertProc 11

    GO

    EXEC #tempInsertProc 12

    GO

    DROP PROC #tempInsertProc

    GO

    SET NOCOUNT OFF

    GO

    Also found few KB articles which list few issues with Temporary Stored Procedure. May not be applicable now (SQL Server 6.5)

    http://support.microsoft.com/kb/197809/j

    http://support.microsoft.com/kb/151536/a

    I will be back if I find anything interesting. 😉

Viewing 7 posts - 1 through 6 (of 6 total)

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