T-SQL

  • Comments posted to this topic are about the item T-SQL

    The Redneck DBA

  • Nice! I almost got tricked. If you had put

    1 Test Policy 150000.00

    2 Policy1 250000.00

    3 0.00

    in the answer, I would have chosen it, but because this answer doesn't exist, it makes me re-read the query again carefully and noticed that the INSERT is actually part of the stored procedure. :hehe:


    Urbis, an urban transformation company

  • As i read through the code, i didn't even realize that the second insert could possibly have been intented to be outside the sp, so in my mind the table had to contain 4 records as the sp was executed 2 times, taking into account that the sp can be executed without any values passed into it due to the default values on the parameters.

    I was actually looking for other kinds of traps 😉

  • Nice Very tricky. I ran each statement separately. I answer it as TABLEA. I read comments posted and tried again, then only I got TABLEC.

  • Iggy (11/13/2008)


    Nice! I almost got tricked. If you had put

    1 Test Policy 150000.00

    2 Policy1 250000.00

    3 0.00

    in the answer, I would have chosen it, but because this answer doesn't exist, it makes me re-read the query again carefully and noticed that the INSERT is actually part of the stored procedure. :hehe:

    I would have fallen into the same boat you would have.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I fell victim to a similair script at one point. :Whistling: As a result I now make it my standard habit that after the declaration of the sp i wrap the entire body in a begin end.

    declare myProc() as

    begin

    end

    Once you stub in the solid open and closing it is very easy to fill in the body and even more difficult to accidentally leave something inside the proc.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I don't agree with calling this tricky. It's only tricky if you start making assumption about what might have been intended as opposed to what is actually there.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • As a result I now make it my standard habit that after the declaration of the sp i wrap the entire body in a begin end.

    that is maybe not a bad habit to develop...:cool:

    it's getting it to become a habit that's the problem:D

  • Nothing like a good trick question on a Friday morning to keep you on your toes!

  • slange (11/14/2008)


    I now make it my standard habit that after the declaration of the sp i wrap the entire body in a begin end.

    declare myProc() as

    begin

    end

    Once you stub in the solid open and closing it is very easy to fill in the body and even more difficult to accidentally leave something inside the proc.

    When you script out stored procedures in SQL 2005, it does not add the 'GO' to the bottom of the script as it did in SQL 2000. Consequently we have developers who script out their stored procedures for the install script and forget to add the 'GO' and end up with bonus commands in their stored procedure (if I don't catch it). So I also made it a best practice to use the begin and end to denote the stored procedure body. This question illustrates the problem.

    Thanks for the question.

  • I've had that happen also, that's what caused me to come up with this question.

    We recently had a deploy script that dropped and recreated several stored procedures something like:

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('SP1') AND type in ('P', 'PC'))

    DROP PROCEDURE SP1

    GO

    CREATE PROCEDURE SP1

    AS

    ...

    GO

    .

    .

    .

    For just one of the SPs, the developers forgot the GO at the end, so one of the SPs got created with the DROP statement for the next SP in it. Apparently the SP that had it's drop added to the stored procedure before it didn't exist, so the script ran without errors. It took me quite a long time to figure out "the mysterious vanishing SP" issue that day!

    The Redneck DBA

  • I am not sure why they changed the scripting so that it does not include the 'GO'? It is such a subtle little change that has caused us a lot of grief.

  • Marius Els (11/14/2008)


    As a result I now make it my standard habit that after the declaration of the sp i wrap the entire body in a begin end.

    that is maybe not a bad habit to develop...:cool:

    it's getting it to become a habit that's the problem:D

    Have you used templates in SSMS? I find them very helpful, especially for documentation headers. I also like the ability to fill in template parameters.

  • Cliff Jones (11/14/2008)


    I am not sure why they changed the scripting so that it does not include the 'GO'? It is such a subtle little change that has caused us a lot of grief.

    I agree and I also miss the ability to have SSMS automatically script out the if exists drop at the beginning of the script as well!

  • cmcklw (11/14/2008)


    Cliff Jones (11/14/2008)


    I am not sure why they changed the scripting so that it does not include the 'GO'? It is such a subtle little change that has caused us a lot of grief.

    I agree and I also miss the ability to have SSMS automatically script out the if exists drop at the beginning of the script as well!

    Yes, that is my other complaint. It used to be so simple to script out a single stored procedure in SQL 2000 and the result included the if exists and drop statement. It is much more cumbersome to do that in SQL 2005.

Viewing 15 posts - 1 through 15 (of 22 total)

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