Voice of the DBA

Tag: T-SQL

  • The Basic TRY..CATCH

    Have you written a TRY..CATCH statement in T-SQL? I hadn’t done it for most of my career, since the construct hadn’t existed. As a result, my code over the years is littered with catching @@error in a variable and then acting on that result. 

    However I’m trying to do better, and when I went to write one recently, I realized that I wasn’t doing it enough as I needed to check some syntax. Here’s a short post to try and capture that information and burn it into my brain.

    The Syntax

    The basic syntax is this:

    BEGIN TRY

    — do some work here.

    END TRY

    BEGIN CATCH

    — error handling code here.

    END CATCH

    This almost seems funny as I’d expect a TRY with a BEGIN END block in the SQL language, but this reads better, and I think this is (Syntactically) a better implementation in the language.

    Using TRY . . CATCH

    The use of this is to do some work in the TRY block (BEGIN TRY..END TRY) and expect it to work. For example, I recently had this:

    BEGIN TRY
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
    END TRY

    BEGIN CATCH

    — CATCH BLOCK

    END CATCH

    SELECT @@rowcount

    The TRY block is the place where I perform some work. If it works as expected, then I just continue on. In this case, this should be a simple query that runs, and when it finishes, the SELECT for the rowcount executes.

    However, if some error occurs, execution immediately goes to the CATCH block. In that case, whatever I have in that space will execute and then the execution will continue.

    Example

    Let’s look at an example of how this works. Here’s my full TRY..CATCH with a few print statements to track the activity.

    ALTER PROCEDURE spGetCommission
    @userid INT
    AS
    PRINT ‘Before TRY’;

        BEGIN TRY
            PRINT ‘Start TRY’;
            SELECT TOP 10
                    cs.CustomerID
                ,   cs.LastSale
                ,   cs.Salesman
                ,   CAST(cs.SaleValue AS NUMERIC)
                FROM
                    dbo.CustomerSales AS cs;
            PRINT ‘End TRY’;
        END TRY
        BEGIN CATCH
            PRINT ‘Start CATCH’;
            THROW 51000, ‘A calculation error occurred’, 1;
            PRINT ‘End CATCH’;
        END CATCH;

    PRINT ‘End of proc’;

    GO

    If I not execute this, with a parameter, I get this:

    Before TRY
    Start TRY

    (0 row(s) affected)
    Start CATCH
    Msg 51000, Level 16, State 1, Procedure spGetCommission, Line 20
    A calculation error occurred

    That might not be what you expected. The TRY works as expected, with the error in my query sending execution to the CATCH block, before the final print statement in the TRY block.

    However I didn’t get the complete execution of the CATCH block, as the THROW throws an error and completes its execution. If I changed this to not re-throw the error, the final statement executes.

    ALTER PROCEDURE spGetCommission
    …

        BEGIN CATCH
            PRINT ‘Start CATCH’;
            PRINT    ‘A calculation error occurred’
            PRINT ‘End CATCH’;
        END CATCH;

    PRINT ‘End of proc’;

    GO

    In this case, I’ll get all my print statements.

    Before TRY
    Start TRY

    (0 row(s) affected)
    Start CATCH
    A calculation error occurred
    End CATCH
    End of proc

    A basic look at TRY..CATCH, and worth knowing about. I’d suggest you use this in future code, and even refactor code where you can to include this instead of looking at @@error to trap issues.

  • Are There That Many GUIDs?

    This editorial was originally published on Oct 12, 2010. It is being re-run as Steve is away on vacation.

    Do a lot of people actually use GUIDs as Primary Keys? I haven’t used them much, and I would have thought that more people chose identity keys. It seems that most of the demos and examples I see from bloggers and speakers are constantly using identities.

    However an informal survey from Peter Bromberg showed that four times as many people actually had GUIDs as their primary keys. The blog actually says that GUIDs are not a good choice, but I’m not sure I agree with that. You can use sequential GUIDs, and you can avoid making them the clustered key, so I think they can work as well as anything.

    There’s nothing inherently wrong with GUIDs, and they should be unique across all of your rows. There have been some reported cases of duplicates, but for most practical purposes, especially in database work, you ought to be able to count on a GUID as unique. They even have the nice capability of being generated by clients, removing the need for an extra round trip when a client needs to insert multiple rows.

    I typically don’t use them because they’re long, hard to remember and type, and hard to view on the screen. I can’t easily compare rows in multiple tables, and it’s easier for me to work with integers.  I don’t recommend them, but if you are going to use them, be sure you understand the pros and cons, and use them appropriately.

     

  • Common Mistakes

    At times I am rather dismayed by the quality of code I see written today. I’m not sure it’s worse than the poor code compiled early in my career, but there are so many more people writing code in our industry that it seems there is more and more poorly written code.

    We suffer from the chef problem. As more companies look to become software companies, they need to hire more software people. To meet the staffing demand, more and more marginally skilled people will be chosen, and software quality goes down.

    Part of what we do here is to try and educate the SQL Server professionals on how to become better at their jobs. That’s really the core mission that started SQLServerCentral and continues today thanks to the belief in that mission by Red Gate Software. As we look to do that, we want to bring to light the things that aren’t good ideas and can cause problems.

    What common mistakes do you see T-SQL developers making?

    The question this week is based on a post by the talented Doug Lane, who wrote about the top three mistakes T-SQL developers make. Doug has a good list, and I’d urge you to read it, along with some sage advice from Brad McGeHee. However I’m sure many of you see different common issues in your own work.

    What things need to be fixed later? What code regularly causes performance issues? The more specific problems that you can share, along with their solutions, the more you might help another developer build better code in the future.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • ALTER SCHEMA TO ADD PERMISSIONS

    I’m sure some of you have wanted to do this:

    ALTER SCHEMA Steve AUTHORIZATION Steve

    You realize this doesn’t work, and you can’t grant the user Steve, rights to his schema after it’s created. You can do this:

    CREATE SCHEMA Steve Authorization Steve

    UPDATE: Someone pointed out this works after the fact:

    ALTER AUTHORIZATION ON SCHEMA::Steve TO Steve

    But not alter it. Strange and annoying. In my last post, I showed dropping and recreating the schema. That works well if you are beginning development, but not when you’re in the middle.

    Let’s make this less confusing and see how we actually allow a developer to access a schema to create procedures (or other objects) when the schema exists.

    First, let’s assume we want a developer, Steve, to be able to create procedures in the ETL schema. We have these conditions:

    • The ETL schema exists
    • The ETL schema is owned by another developer.
    • The login and user, Steve, exists in this database with no permissions.

    I want to now allow Steve to build the procedure ETL.MyProc.

    Grant Permissions

    The first thing I do is grant create procedure permissions to Steve.

    CREATE LOGIN steve WITH PASSWORD = ‘Test’;
    GO
    USE Sandbox
    GO
    CREATE USER Steve FOR LOGIN Steve
    GO
    GRANT CREATE PROCEDURE to Steve;

    GO

    With this done, now let’s set up our schema.

    CREATE SCHEMA ETL
    GO
    There are no default permissions, so the user Steve cannot create ETL.MyProc right now. How do we fix this?

    The trick here is that I need to allow Steve to ALTER the schema. I can do this by using this statement.

    GRANT ALTER ON SCHEMA::ETL TO Steve;
    GO

    I could do other things. I could grant CONTROL. to Steve instead, but I might not want to do that. That gives Steve the ability to actually drop the schema, which probably isn’t want. It’s certainly not the “least permissions” to let the developer create objects in a schema.