Stored procedures

  • anders-731262

    Ten Centuries

    Points: 1256

    Comments posted to this topic are about the item Stored procedures

  • Artur Sokhikyan

    SSC Eights!

    Points: 840

    nice question

    -------------------------
    - Name?
    - Abu Dalah Sarafi.
    - Sex?
    - 3 times a week!
    - No, no. Male or Female?
    - Male, female, sometimes camel...

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    damn, really nice question, completely threw me. I was thinking about the fact that "sp_*" procs still need an extra property defined in order to act on objects (tables etc) that belong in the current database.

    This sample illustrates what messed me up:

    use master

    go

    create table LocalTable (testcolumn nVarChar(255))

    go

    INSERT INTO LocalTable SELECT 'This data is in master!'

    go

    create proc sp_test as

    select db_name() AS DBNameFromFunction,

    (select count(*) from sysobjects) AS ObjectCountFromSystemView,

    (SELECT TOP 1 testcolumn FROM LocalTable) AS DBNameFromLocalObject

    go

    create database testdb

    go

    use testdb

    go

    create table LocalTable (testcolumn nVarChar(255))

    go

    INSERT INTO LocalTable SELECT 'This data is in testdb!'

    go

    sp_test

    go

    use master

    go

    sp_test

    go

    use master

    go

    drop database testdb

    drop proc sp_test

    drop table LocalTable

    go

    You expect the "LocalObject" result column to show the results of that table in the current context database, but it does not - it shows you the contents of that table in the master database!

    I had a very hard time understanding (or finding any documentation / help on) this behaviour when I first started writing "sp_*" procs, but I haven't looked in a while; I guess it's generally discouraged anyway 🙂

    If you really do want to change this behaviour (and see the correct result in the "LocalObject" column returned by the proc above), you need to use the "sp_ms_marksystemobject" stored procedure. Please note, it only makes sense to do this if you know that your proc will only ever be called from databases that will contain the local tables you are referencing.

    In my case it makes sense for some management procs that need to work on dozens or hundreds of databases with essentially the same structure - it helps avoid using less-safe and harder-to-read "EXEC()" or "sp_executesql" calls.

    (I was tempted to go write my own version of this QotD with the "trick", but felt it infringed a little on Anders' question - maybe something like that is coming anyway)

    Hope this helps someone,

    Tao

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    Artur Sokhikyan (8/4/2009)


    nice question

    -------------------------

    - Name?

    - Abu Dalah Sarafi.

    - Sex?

    - 3 times a week!

    - No, no. Male or Female?

    - Male, female, sometimes camel...

    Strong!!!!

  • jjtetzlo

    SSCrazy

    Points: 2481

    For those that have html tags inside the question (I'm using Chrome), the question shows up properly in the email... and I posted it below:

    use master

    go

    create database testdb

    go

    create proc sp_test as

    select db_name()

    go

    sp_test

    go

    use testdb

    go

    sp_test

    go

    use master

    go

    drop database testdb

    drop proc sp_test

    go

    - Jeff

  • Cliff Jones

    SSChampion

    Points: 10517

    Yes, really nice question that illustrates the perils of naming a stored procedure using the naming convention 'sp_' which is used for system stored procedures.

  • SanjayAttray

    SSChampion

    Points: 13157

    Artur Sokhikyan (8/4/2009)


    - Name?

    - Abu Dalah Sarafi.

    - Sex?

    - 3 times a week!

    - No, no. Male or Female?

    - Male, female, sometimes camel...

    ROFL .... Your signature made my day.

    SQL DBA.

  • Cliff Jones

    SSChampion

    Points: 10517

    Yes, I am a little worried about Artur. 😎

  • webrunner

    SSC-Dedicated

    Points: 30306

    Thanks, great question. Usually "great question" means one where I don't know enough to get it right, so I was happy to get this one right, and for the right reason!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • RBarryYoung

    SSC Guru

    Points: 143327

    FYI: the BOL link posted after the explanation does not confirm anything said in the explanation.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • TomThomson

    SSC Guru

    Points: 104773

    Well, I know what is supposed to happen so I got my point. But anyone still using SQLServer 2000 should be warned about this - the behaviour is erratic, it may decide that although the proc name begins sp_ it's not going to look for it in master. So it's best always to use exec master..sp_ (in modern SQLServer, exec sys.sp_ - this is explicitly recommended in BOL for 2008) rather than just sp_.

    Tom

  • Bhavesh_Patel

    SSCrazy

    Points: 2259

    Good Q.

    Learned something new today.



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    Pretty question

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

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