create Procedure within Procedure

  • Hi,

    I want to create procedure within procedure

    something like that

    create procedure aa

    as

    begin

    if exists (select * from sys.objects where name='bb')

    begin

    drop procedure bb

    go

    declare @a nvarchar(max)

    set @a='create procedure bb as begin print ''bb''end'

    end

    end

    Thanks in Advance

  • Is there a question here? Your code looks like it should work, just add an EXEC to run the dynamic sQL you generated.

    I do have to ask... Why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Thanks for reply

    create procedure aa

    as

    begin

    if exists (select * from sys.objects where name='bb')

    begin

    drop procedure bb

    go

    declare @a nvarchar(max)

    set @a='create procedure bb as begin print ''bb'' end'

    exec sp_executesql @a

    end

    end

    after run this code i got following error message

    Msg 102, Level 15, State 1, Procedure aa, Line 9

    Incorrect syntax near 'bb'.

    Msg 156, Level 15, State 1, Line 7

    Incorrect syntax near the keyword 'end'.

  • Well, the GO in the 'middle' of procedure A ends the batch. Hence leaving a begin without an end and, in the second batch an end without a begin.

    GO is a batch breaker. It tells SSMS where to delimit the batches sent to SQL. So what you're telling SSMS is that the first batch it must send is

    create procedure aa

    as

    begin

    if exists (select * from sys.objects where name='bb')

    begin

    drop procedure bb

    GO

    As I'm sure you can see, that is not valid SQL.

    Again, I need to ask why you want a procedure to create a procedure? What's the point?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • or it shud be like below

    create procedure aa

    as

    begin

    if exists (select * from sys.objects where name='bb')

    begin

    drop procedure bb

    declare @a nvarchar(max)

    set @a='create procedure bb as begin print ''bb'' end'

    exec sp_executesql @a

    end

    end

    But as Gail's suggestion.... why u want to do this??? is this some class room based question???

Viewing 5 posts - 1 through 4 (of 4 total)

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