Stored procedures

  • Comments posted to this topic are about the item Stored procedures

  • nice question

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

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

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

  • 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

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

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

  • Yes, I am a little worried about Artur. 😎

  • 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

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

  • 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

  • Good Q.

    Learned something new today.



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

    http://bhaveshgpatel.wordpress.com/
  • Pretty question

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

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