Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Question Unanswered: Create Procedure #Test in another SP?

    Hi,

    I was wondering if it's possible to create a local stored procedure within another stored procedure? Something like the following:

    CREATE PROCEDURE [sti_edocsecure].[spPagingDynamicWrapper]

    @Page int,
    @Size int,
    @ItemStatus int,
    @UserId int,
    @TableName varchar(255),
    @Select varchar(8000),
    @Criteria varchar(8000)

    AS

    BEGIN TRANSACTION

    SET NOCOUNT ON

    CREATE PROCEDURE #Test
    @Page int,
    @Size int,
    @ItemStatus int,
    @UserId int,
    @TableName varchar(255),
    @Select varchar(8000),
    @Criteria varchar(8000)

    AS

    BEGIN TRANSACTION

    SET NOCOUNT ON

    RETURN 0

    COMMIT TRANSACTION
    Return 0

    COMMIT TRANSACTION
    GO

    According to MSDN you can do this but I can't find any samples. The above SP gives me an error saying:

    Error 156: Incorrect syntax near the keyword 'PROCEDURE'.

    So, I don't know if I'm doing something wrong or if this is even possible.

    Thanks for any insight anyone can provide,

    Craig

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Not quite sure why U'd want 2 but

    declare @sql nvarchar(500)
    select @sql = 'Create Procedure AAA As SELECT 1 as one'

    exec sp_executesql @sql

    Handy little sp this one [sp_executesql]

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •