Stored Proc with SP_ prefix

  • I completely agree with the fact that stored proc with the prefix "sp_" is reserved for system stored procs. However, the 2nd part is not correct.

    If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will never get executed.

    To verify this I've executed the below code, which suggest irrespective of the prefix, it’s going to execute the proc which has been referred.

    T-SQL Code

    use master

    drop proc dbo.sp_test

    go

    create proc dbo.sp_test

    as

    print 'SP from Master DB'

    go

    use AdventureWorks

    go

    drop proc dbo.sp_test

    go

    create proc dbo.sp_test

    as

    print 'SP from AdvWork DB'

    go

    exec dbo.sp_test

    Result

    SP from AdvWork DB

    Thanks,

    Ashesh


    Thanks,
    Ashesh

Viewing 0 posts

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