{"id":1941,"date":"2015-01-26T00:00:00","date_gmt":"2015-01-26T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/questions-about-t-sql-control-of-flow-language-you-were-too-shy-to-ask\/"},"modified":"2021-09-29T16:21:31","modified_gmt":"2021-09-29T16:21:31","slug":"questions-about-t-sql-control-of-flow-language-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-about-t-sql-control-of-flow-language-you-were-too-shy-to-ask\/","title":{"rendered":"Questions About T-SQL Control-of-Flow Language You Were Too Shy to Ask"},"content":{"rendered":"
\n
    \n
  1. “Do I always need to use a BEGIN…END statement block within an IF statement?”<\/a><\/li>\n
  2. “When should I include an ELSE clause within an IF statement?”<\/a><\/li>\n
  3. How do I nest IF and IF…ELSE statements, if it’s even possible?”<\/a><\/li>\n
  4. “Is it possible to terminate a procedures execution from within an IF statement?”<\/a><\/li>\n
  5. “Which is better to use, a CASE expression or an IF (or IF…ELSE) statement?”<\/a><\/li>\n
  6. “Can I use control-of-flow language to do an upsert in SQL Server?”<\/a><\/li>\n
  7. “I don’t get how a WHILE loop works. Can you help make sense of what’s going on here?”<\/a><\/li>\n
  8. “How do I add a pause or wait time to my T-SQL in order to delay execution?”<\/a><\/li>\n
  9. “Is it possible to skip from one section of your T-SQL code to another?”<\/a><\/li>\n
  10. “What’s the simplest way to incorporate error handling into my stored procedures?”<\/a><\/li>\n<\/ol>\n

    “Do I always need to use a BEGIN...END<\/code> statement block within an IF statement?”<\/h3>\n

    You need to include the BEGIN...END<\/code><\/b> block only if you want to associate more than one T-SQL statement with the IF<\/code><\/b> condition. The condition is a Boolean expression within the IF<\/code><\/b> clause that directs statement execution control flow. The query engine evaluates the condition and, if it evaluates to TRUE<\/code><\/b>, runs the statement immediately following the IF<\/code><\/b> clause. Otherwise, the query engine skips ahead to the appropriate next statement.<\/p>\n

    If you want that condition to apply to multiple statements, you must treat them as a statement block by enclosing them within the BEGIN<\/code><\/b> and END<\/code><\/b> keywords. Let’s look at a few IF<\/code><\/b> statements in action to better understand how the execution flow works. The following stored procedure includes the logic necessary to check whether the @CustID<\/code><\/b> variable is NULL<\/code><\/b>:<\/p>\n

    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  PRINT 'You must provide a customer ID.';\nSELECT FirstName, LastName, City, StateProvinceName\nFROM Sales.vIndividualCustomer\nWHERE BusinessEntityID = @CustID;\nGO\n\n<\/pre>\n
    The  IF<\/code><\/b> expression verifies whether the variable  value is NULL<\/code><\/b>. If it is, the expression  evaluates to TRUE<\/code><\/b>, and the query processor  executes the PRINT<\/code><\/b> statement. Otherwise, the  processor skips the PRINT<\/code><\/b>  statement and jumps to the SELECT<\/code><\/b> statement. We can verify  this by calling the procedure and passing in a valid  BusinessEntityID<\/code><\/b> value:<\/p>\n
    EXEC GetCustomerInfo 1700;<\/pre>\n
    In this case, the EXECUTE<\/code><\/b> statement (and subsequently  the procedure) returns the results shown in the following table:<\/p>\n\n\n\n\n
    \n
    FirstName<\/b><\/p>\n<\/td>\n
    		\n
    LastName<\/b><\/p>\n<\/td>\n
    		\n
    City<\/b><\/p>\n<\/td>\n
    		\n
    StateProvinceName<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Rebecca<\/p>\n<\/td>\n
    		\n
    Robinson<\/p>\n<\/td>\n
    		\n
    Seaford<\/p>\n<\/td>\n
    		\n
    Victoria<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Next let’s call the procedure without specifying a  BusinessEntityID<\/code><\/b>  value:<\/p>\n
    EXEC GetCustomerInfo;<\/pre>\n
    Because the @CustID<\/code><\/b> value is  NULL<\/code><\/b>,  the query engine executes the  PRINT<\/code><\/b> statement and returns the following  message, along with an empty result set for the customer data:<\/p>\n
    You must provide a customer ID.<\/pre>\n
    So far, this is all fairly straightforward, but now suppose our procedure includes additional  statements that should run if the  IF<\/code><\/b> expression evaluates to  TRUE<\/code><\/b>:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  PRINT 'You must provide a customer ID.';\n  PRINT 'Contact the sales rep for your region:';\n  SELECT FirstName, LastName, TerritoryName, EmailAddress\n  FROM Sales.vSalesPerson\n  WHERE JobTitle = 'Sales Representative';\nSELECT FirstName, LastName, City, StateProvinceName\nFROM Sales.vIndividualCustomer\nWHERE BusinessEntityID = @CustID;\nGO\n<\/pre>\n
    This time around, we want to run two  PRINT<\/code><\/b> statements as well as a  SELECT<\/code><\/b>  statement that returns a list of sales representations. When we call the stored procedure without specifying a  <\/a> BusinessEntityID<\/code><\/b> value<\/a>,  we receive the two messages, as expected:<\/p>\n
    You must provide a customer ID.\nContact the sales rep for your region:\n\n<\/pre>\n
    In addition, we receive the following information about the sales reps:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FirstName<\/b><\/p>\n<\/td>\n
    		\n
    LastName<\/b><\/p>\n<\/td>\n
    		\n
    TerritoryName<\/b><\/p>\n<\/td>\n
    		\n
    EmailAddress<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael<\/p>\n<\/td>\n
    		\n
    Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n
    		\n
    michael9@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda<\/p>\n<\/td>\n
    		\n
    Mitchell<\/p>\n<\/td>\n
    		\n
    Southwest<\/p>\n<\/td>\n
    		\n
    linda3@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian<\/p>\n<\/td>\n
    		\n
    Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n
    		\n
    jillian0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Garrett<\/p>\n<\/td>\n
    		\n
    Vargas<\/p>\n<\/td>\n
    		\n
    Canada<\/p>\n<\/td>\n
    		\n
    garrett1@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi<\/p>\n<\/td>\n
    		\n
    Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n
    		\n
    tsvi0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela<\/p>\n<\/td>\n
    		\n
    Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    Northwest<\/p>\n<\/td>\n
    		\n
    pamela0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu<\/p>\n<\/td>\n
    		\n
    Ito<\/p>\n<\/td>\n
    		\n
    Southwest<\/p>\n<\/td>\n
    		\n
    shu0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    José<\/p>\n<\/td>\n
    		\n
    Saraiva<\/p>\n<\/td>\n
    		\n
    Canada<\/p>\n<\/td>\n
    		\n
    josé1@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    David<\/p>\n<\/td>\n
    		\n
    Campbell<\/p>\n<\/td>\n
    		\n
    Northwest<\/p>\n<\/td>\n
    		\n
    david8@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete<\/p>\n<\/td>\n
    		\n
    Mensa-Annan<\/p>\n<\/td>\n
    		\n
    Northwest<\/p>\n<\/td>\n
    		\n
    tete0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Lynn<\/p>\n<\/td>\n
    		\n
    Tsoflias<\/p>\n<\/td>\n
    		\n
    Australia<\/p>\n<\/td>\n
    		\n
    lynn0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Rachel<\/p>\n<\/td>\n
    		\n
    Valdez<\/p>\n<\/td>\n
    		\n
    Germany<\/p>\n<\/td>\n
    		\n
    rachel0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jae<\/p>\n<\/td>\n
    		\n
    Pak<\/p>\n<\/td>\n
    		\n
    United Kingdom<\/p>\n<\/td>\n
    		\n
    jae0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ranjit<\/p>\n<\/td>\n
    		\n
    Varkey Chudukatil<\/p>\n<\/td>\n
    		\n
    France<\/p>\n<\/td>\n
    		\n
    ranjit0@adventure-works.com<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Plus, we receive an empty result set for the customer information. Now let’s look what  happens when we call the procedure and pass in a correct  BusinessEntityID<\/code><\/b> value:<\/p>\n
    EXEC GetCustomerInfo 1700;<\/pre>\n
    As expected, we won’t receive the message about providing a customer ID, but we’ll still  receive the message about contacting a sales rep as well as the result set that contains the sales rep information. Only  then will we receive the result set that contains the customer information, which is the only data we really wanted.<\/p>\n
    The problem, of course, is that the query engine associates only the first  PRINT<\/code><\/b>  statement with the IF<\/code><\/b>  condition. If we want to associate additional statements, we must enclose all the statements within a  BEGIN...END<\/code><\/b>  block:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n  END\nSELECT FirstName, LastName, City, StateProvinceName\nFROM Sales.vIndividualCustomer\nWHERE BusinessEntityID = @CustID;\nGO\n<\/pre>\n
    If we now call the stored procedure without specifying a customer ID, we’ll receive both  messages, a result set that contains the sales rep information, and an empty result set for the customer information.  However, if we do provide a valid customer ID, the query will return only the customer information.<\/p>\n
    One other note about using BEGIN...END<\/code><\/b>. Many developers will  enclose even a single statement in  BEGIN...END<\/code><\/b> because it makes the code more  readable and makes it less likely that another developer will introduce an error by adding a statement without the  block. In general, it doesn’t hurt to include the  BEGIN...END<\/code><\/b> block even if you don’t need it  in a particular case. In the long run it could save everyone a lot of trouble.<\/p>\n
    “When should I include an  ELSE<\/code>  clause within an  IF statement?”<\/h3>\n
    You can add an ELSE<\/code><\/b> clause to any  IF<\/code><\/b>  construction when you want to run specific statements in the event the Boolean expression (the  IF<\/code><\/b>  condition) evaluates to FALSE<\/b>. You specify the  ELSE<\/code><\/b>  clause after the IF<\/code><\/b>  statement block. An ELSE<\/code><\/b>  clause let’s you better control your code’s logic and provides a mechanism for running statements outside of the  IF...ELSE<\/code><\/b>  structure.<\/p>\n
    Let’s again rewrite our stored procedure. This time, we’ll put the logic for retrieving the  customer information in an  ELSE<\/code><\/b> clause and then add a final PRINT<\/code><\/b> statement after the  IF...ELSE<\/code><\/b>  block:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n  END\nELSE\n  SELECT FirstName, LastName, City, StateProvinceName\n  FROM Sales.vIndividualCustomer\n  WHERE BusinessEntityID = @CustID;\nPRINT 'AdventureWorks Bicycles;  ' \n  + CONVERT(VARCHAR(30), GETDATE(), 109);\nGO\n<\/pre>\n
    As in the previous example, if the  IF<\/code><\/b> expression evaluates to  TRUE<\/code><\/b>,  the statements within the  BEGIN...END<\/code><\/b> block will run. However, this time around, if the expression evaluates to  FALSE<\/code><\/b>,  the SELECT<\/code><\/b>  statement in the ELSE<\/code><\/b>  clause will run. This approach let’s us be more precise with our logic while at the same time add other statements that  are unrelated to the  IF...ELSE<\/code><\/b> statements.<\/p>\n
    In this case, we’ve added a final  PRINT<\/code><\/b> statement, which will run regardless  of what statements run within the  IF...ELSE<\/code><\/b> blocks. That means, when we call  the function and pass in a valid customer ID, the procedure will return only the customer data along with the following  information:<\/p>\n
    AdventureWorks Bicycles;  Jan 18 2015  9:54:16:237AM<\/pre>\n
    However, if we call the procedure without specifying a customer ID, it will return the  original two statements about providing a customer ID and contacting the sales rep as well as return the result set  containing the sales rep information. The results will also include the statement about AdventureWorks Bicycles, but  will not include the empty result set for the customer information. Because we’ve added the  ELSE<\/code><\/b> clause, the clause’s  SELECT<\/code><\/b>  statement is no longer executed when the  IF<\/code><\/b> expression evaluates to  TRUE<\/code><\/b>.  The query engine executes the  ELSE<\/code><\/b> clause only<\/i> if the related  IF<\/code><\/b> expression evaluates to  FALSE<\/code><\/b>.<\/p>\n
    “How do I nest  IF<\/code>  and  IF…ELSE statements,  if it’s even possible?”<\/h3>\n
    Yes, it is possible to nest your  IF<\/code><\/b> and  IF...ELSE<\/code><\/b>  statements, and you can do so to whatever degree necessary and practical. The primary limitation on nesting is available  memory. <\/p>\n
    To demonstrate how nesting works, let’s return to the  GetCustomerInfo<\/code><\/b>  stored procedure. As you’ll recall from the last couples examples, we used the  IF<\/code><\/b>  conditions to determine which statements to run based on the value of the  @CustID<\/code><\/b> variable. If the value was  NULL<\/code><\/b>, we ran one set of statements.  If the value was an integer, we ran a different statement. However, what if the value is a valid integer but not a valid  customer ID?<\/p>\n
    To address this situation, we can add logic to our T-SQL code that handles the additional  scenario. One way to do this is to nest an  IF...ELSE<\/code><\/b> statement within the outer  ELSE<\/code><\/b>  clause, as shown in the following example:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n  END\nELSE\n  BEGIN\n    IF EXISTS(SELECT * FROM Sales.vIndividualCustomer\n        WHERE BusinessEntityID = @CustID)\n      SELECT FirstName, LastName, City, StateProvinceName\n      FROM Sales.vIndividualCustomer\n      WHERE BusinessEntityID = @CustID;\n    ELSE\n      PRINT 'No record matches the customer ID ' + \n        CAST(@CustID AS VARCHAR(10)) + '.'\n  END\nGO\n<\/pre>\n
    Notice we use the EXISTS<\/code><\/b> function to check whether  the ID exists. As a result, the  SELECT<\/code><\/b> statement in the original  ELSE<\/code><\/b> clause will run only if the @CustID<\/code><\/b> value matches a value in  underlying table. For example, suppose we pass in the value  101<\/code><\/b> when we call the stored procedure. If  there is no match, it will return only the following message:<\/p>\n
    No record matches the customer ID 101.<\/pre>\n
    Because the first IF<\/code><\/b> expression evaluated to  FALSE<\/code><\/b>,  the ELSE<\/code><\/b>  clause kicked in. However, the nested IF<\/code><\/b> expression in the outer  ELSE<\/code><\/b>  clause also evaluated to  FALSE<\/code><\/b>, so the query engine jumped to the nested  ELSE<\/code><\/b>  clause and executed that  PRINT<\/code><\/b> statement.<\/p>\n
    Clearly, the ability to nest  IF...ELSE<\/code><\/b> statements provides a powerful  mechanism for adding complex logic to our stored procedures and batches. Be wary of overdoing it, however. Memory is not  the only issue to keep in mind. Too much conditional logic can result in the query optimizer having to continuously  re-cache the plan, which can severely impact performance. Complex logic is often better suited to the application layer,  letting each stored procedure focus on a single task.<\/p>\n
    “Is it possible to terminate a procedure’s execution  from within an  IF  statement?”<\/h3>\n
    Yes, it is possible, and often it’s a good strategy to do so. Why cause the query engine to  do any more work than necessary? The sooner you can pull out, the better. And T-SQL supports a great tool for  unconditionally exiting a batch, statement block, or stored procedure: the  RETURN<\/code><\/b>  statement.<\/p>\n
    You can add a RETURN<\/code><\/b> statement to whatever part  of the T-SQL logic that you want to end when and if you reach that part of the code. Let’s return to a basic version of  our GetCustomerInfo<\/code><\/b>  stored procedure:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n  END\nSELECT FirstName, LastName, City, StateProvinceName\nFROM Sales.vIndividualCustomer\nWHERE BusinessEntityID = @CustID;\nGO\n<\/pre>\n
    Even if the IF<\/code><\/b> expression evaluates to  TRUE<\/code><\/b>,  the final SELECT<\/code><\/b>  statement runs and with it comes an empty result set. One way to avoid this, as we saw earlier, is to add an  ELSE<\/code><\/b>  clause. However, we can also terminate the stored procedure after the  IF<\/code><\/b> statement block runs by adding the RETURN<\/code><\/b> statement:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n    RETURN;\n  END\nSELECT FirstName, LastName, City, StateProvinceName\nFROM Sales.vIndividualCustomer\nWHERE BusinessEntityID = @CustID;\nGO\n<\/pre>\n
    If the  IF<\/code><\/b> expression evaluates to  TRUE<\/code><\/b>  all the statements within the  BEGIN...END<\/code><\/b> block will run, just like before;  however, when the query engine hits  RETURN<\/code><\/b>, it will immediately stop processing  the stored procedure and exit. No other statements will be executed. <\/p>\n
    Although this might not always be the strategy you want to employ for controlling your code’s  logical flow, when it is, the  RETURN<\/code><\/b> statement is easy to implement and  can be used anywhere in your code that makes sense. For example, the following procedure definition also includes  RETURN<\/code><\/b>  in the nested IF<\/code><\/b>  statement:<\/p>\n
    IF OBJECT_ID('dbo.GetCustomerInfo', 'p') IS NOT NULL\nDROP PROCEDURE dbo.GetCustomerInfo;\nGO\n\nCREATE PROCEDURE dbo.GetCustomerInfo\n  (@CustID INT = NULL)\nAS\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n    RETURN;\n  END\nELSE\n  BEGIN\n    IF EXISTS(SELECT * FROM Sales.vIndividualCustomer\n        WHERE BusinessEntityID = @CustID)\n      BEGIN\n        SELECT FirstName, LastName, City, StateProvinceName\n        FROM Sales.vIndividualCustomer\n        WHERE BusinessEntityID = @CustID;\n        RETURN;\n      END\n    ELSE\n      PRINT 'No record matches the customer ID ' + \n        CAST(@CustID AS VARCHAR(10)) + '.'\n  END\nGO\n<\/pre>\n
    As before, the query engine terminates the procedure whenever it encounters  RETURN<\/code><\/b>  and does not run any more statements. <\/p>\n
    “Which is better to use, a  CASE expression or an  IF (or  IF…ELSE)  statement?”<\/h3>\n
    This question comes up often, in part because Microsoft documentation used to group  CASE<\/code><\/b> in with its control-of-flow  language elements. But that has changed because  CASE<\/code><\/b> is not a control-of-flow language  element, but rather a method for evaluating individual expressions. <\/p>\n
    So it’s not quite fair to compare  IF<\/code><\/b> and  CASE<\/code><\/b>.  They serve different purposes and perform different types of operations. The  IF<\/code><\/b>  statement controls the flow of your code’s logic; whereas,  CASE<\/code><\/b> is used as part of an expression to  evaluate a particular value. One way to think of this is it that  IF<\/code><\/b> works at the statement level and  CASE<\/code><\/b>  works at the value level, usually within a SELECT<\/code><\/b> clause or  WHERE<\/code><\/b>  clause. The CASE<\/code><\/b>  expression evaluates a set of conditions sequentially and stops when one of the conditions evaluates to  TRUE<\/code><\/b>.  With an IF<\/code><\/b>  or IF...ELSE<\/code><\/b>  statement, you can better control which elements get executed and in what order that execution occurs.<\/p>\n
    That said, the two can sometimes be used to achieve the same results. For example, the  following T-SQL code declares a variable and uses an  IF<\/code><\/b> statement to verify it’s value:<\/p>\n
    DECLARE @a VARCHAR(30) = 'one'\n\nIF @a = 'one'\n  BEGIN\n    PRINT 'The variable value is 1.';\n    RETURN;\n  END\nIF @a = 'two'\n  BEGIN\n    PRINT 'The variable value is 2.';\n    RETURN;\n  END\nIF @a = 'three'\n  BEGIN\n    PRINT 'The variable value is 3.';\n    RETURN;\n  END\nIF @a = 'four'\n  BEGIN\n    PRINT 'The variable value is 4.';\n    RETURN;\n  END\nIF @a NOT IN ('one', 'two', 'three', 'four')\n  BEGIN\n    PRINT 'The variable value is not 1-4.';\n    RETURN;\n  END\n<\/pre>\n
     Each  IF<\/code><\/b> condition is evaluated until one  evaluates to TRUE<\/code><\/b>.  The query engine will run the associated PRINT<\/code><\/b> statement and  RETURN<\/code><\/b>  statement, thus ending execution. But we can achieve the same results by creating a  CASE<\/code><\/b>  expression within a SELECT<\/code><\/b>  clause:<\/p>\n
    DECLARE @b VARCHAR(30) = 'two'\n\nSELECT @b = \n  CASE \n    WHEN @b = 'one' THEN 'The variable value is 1.'\n    WHEN @b = 'two' THEN 'The variable value is 2.'\n    WHEN @b = 'three' THEN 'The variable value is 3.'\n    WHEN @b = 'four' THEN 'The variable value is 4.'\n  ELSE 'The variable value is not 1-4.'\n  END;\nPRINT @b;\n\n<\/pre>\n
    Once again, the query engine starts by evaluating each condition, but stops when it reaches  the condition that evaluates to  TRUE<\/code><\/b>. Even with this simple example, you  can see how much easier it is to create a  CASE<\/code><\/b> expression. If you can use  CASE<\/code><\/b>,  that’s a good way to go. But if you need to control the flow of the statement logic, you should use  IF<\/code><\/b>  or another control-of-flow language construct. A CASE<\/code><\/b> expression is great for doing  the job it was meant for, but it’s not meant to control statement execution.<\/p>\n
    Rather than comparing whether  CASE<\/code><\/b> is better than  IF<\/code><\/b>,  or vice versa, think in terms of picking the right tool for the right job. Usually, you’ll want to use  CASE<\/code><\/b>  to evaluate and return individual values and use IF<\/code><\/b> to control the flow of statement  execution.<\/p>\n
    “Can I use control-of-flow language to do an upsert<\/i> in SQL Server?”<\/h3>\n
    For those not familiar with the term, upsert<\/i> refers  to code that includes logic to either update a row or insert a row, depending on whether that row exists. As with other  data modification operations, you can use control-of-flow statement elements to do an upsert.<\/p>\n
    One of the most common ways to do this is to use an  IF<\/code><\/b>  expression to check for the row’s existence and then execute your statements accordingly. Let’s start with a simple  temporary table to demonstrate how this works:<\/p>\n
    CREATE TABLE #products\n(ProdID INT, ListPrice MONEY);\n\nINSERT INTO #products VALUES\n(101, 199.99),\n(102, 299.99),\n(103, 399.99);\n\nNow let's create our data modification statements, based on an IF...ELSE construction to control the execution flow:\nDECLARE @ProdID INT = 103;\nDECLARE @ListPrice MONEY = 388.88\n\nIF EXISTS(SELECT * FROM #products \n    WHERE ProdID = @ProdID)\n  BEGIN\n    UPDATE #products\n    SET ListPrice = @ListPrice\n    WHERE ProdID = @ProdID\n  END\nELSE\n  BEGIN\n    INSERT INTO #products\n      VALUES(@ProdID, @ListPrice)\n  END;\n\nSELECT * FROM #products WHERE ProdID = @ProdID;\n\n<\/pre>\n
    The  IF<\/code><\/b> expression uses the  EXISTS<\/code><\/b>  function to determine whether the row exists (based on the product ID). If it does exist, the  UPDATE<\/code><\/b>  statement is executed. Otherwise, the database engine performs the  INSERT<\/code><\/b> statement defined in the  ELSE<\/code><\/b> clause. <\/p>\n
    In this case, the value does exist so the  ListPrice<\/code><\/b> value is updated and the  SELECT<\/code><\/b> statement returns a value of 388.88<\/code><\/b>, as expected. Had we set the  value of the @ProdID<\/code><\/b>  variable to one that doesn’t exist, a new row would have instead been inserted into the table.<\/p>\n
    This, of course, is a very simple example, but it demonstrates what is a common approach to  using an IF...ELSE<\/code><\/b> statement to perform an  upsert. And you can just as easily use control-of-flow language for other types of data modifications.<\/p>\n
    But you need to be careful when you start to mix-and-match in this way. For example, the  solution above could result in concurrency issues, either in the form of deadlocks or multiple insertions of the same  data. In addition, this approach might not perform as well as other options because of unnecessary table or index scans.  Workarounds to this approach might include using the  @@rowcount<\/code><\/b> system variable to test whether  the update has worked or using a  MERGE<\/code><\/b> statement to join the table to  itself. The point is, you must be especially diligent when using control-of-flow language to modify data.<\/p>\n
    “I don’t get how a  WHILE<\/code>  loop works. Can you help make sense of what’s going on here?”<\/h3>\n
    The  WHILE<\/code><\/b> loop provides a structure for  repeatedly executing a set of statements as long as the loop’s condition evaluates to  TRUE<\/code><\/b>.  The WHILE<\/code><\/b>  loop lets you add the logic necessary to limit the number of executions when you don’t know that number in advance.  Let’s look at a few examples to get a sense of how these loops work.<\/p>\n
    We’ll start simply by first demonstrating the logic behind the execution flow when using a WHILE<\/code><\/b> loop. The following T-SQL  declares a variable and then executes a loop based on the variable’s value:<\/p>\n
    DECLARE @c INT = 100;\n\nWHILE @c <= 104\n  BEGIN\n    PRINT @c;\n    SET @c = @c + 1; \n  END;\n\n<\/pre>\n
    The loop begins with the WHILE<\/code><\/b> keyword, followed by the  condition that is evaluated each time the loop runs. If the condition evaluates to  TRUE<\/code><\/b>, the query engine executes the  T-SQL in the statement block that follows the condition. If the condition evaluates to  FALSE<\/code><\/b>,  the query engine exits the loop and continues on to any code after the statement block. <\/p>\n
    In this case, the condition will evaluate to  TRUE<\/code><\/b> five times, which means the loop will  run five times. When the variable value exceeds  104<\/code><\/b>, the loop ends. The following statement  shows the results that the statements return:<\/p>\n
    \t\t100\n101\n102\n103\n104\n\t<\/pre>\n
    You can further control the logic within your loop by adding a  BREAK<\/code><\/b>  statement in any place where the query engine should exit the loop:<\/p>\n
    DECLARE @d INT = 100;\n\nWHILE @d <= 104\n  BEGIN\n    PRINT @d;\n    SET @d = @d + 1;\n    IF @d = 103\n      BREAK;\n  END;\n\n<\/pre>\n
    This time around, we’ve added an  IF<\/code><\/b> statement that specifies that the query  engine should break out of the loop if the variable value equals 103<\/code><\/b>. Because we’ve added the  IF<\/code><\/b> and  BREAK<\/code><\/b>  conditions, our loop now returns only the following results;<\/p>\n
    100\n101\n102\n\t<\/pre>\n
    As you can see, query execution stopped when the variable value hit  103<\/code><\/b>.  <\/p>\n
    You can also add a CONTINUE<\/code><\/b> statement to your  WHILE<\/code><\/b>  loop to further control the execution. The CONTINUE<\/code><\/b> statement causes the query  engine to restart the loop from wherever you’ve added  CONTINUE<\/code><\/b>:<\/p>\n
    DECLARE @e INT = 100;\n\nWHILE @e <= 104\n  BEGIN\n    PRINT @e;\n    SET @e = @e + 1; \n    CONTINUE;\n  IF @e = 103\n    BREAK;\n  END;\n\n<\/pre>\n
    Because I’ve included the CONTINUE<\/code><\/b> statement where I did, the  statement will never reach the final  IF<\/code><\/b> block. Instead, each time the  WHILE<\/code><\/b> condition evaluates to  TRUE<\/code><\/b>,  the statement block will be executed up to CONTINUE<\/code><\/b> and then start over,  giving us the following results:<\/p>\n
    100\n101\n102\n103\n104\n\t<\/pre>\n
    Of course, all we’ve done is go full circle, receiving the same results we received without  using CONTINUE<\/code><\/b> and  BREAK<\/code><\/b>,  but these simple examples should help demonstrate the basic concepts of a  WHILE<\/code><\/b> loop. <\/p>\n
    But now suppose we want to do something a little more involved. Let’s start by creating a  small temporary table based on data from the  Product<\/code><\/b> table in the  AdventureWorks2014<\/code><\/b>  database: <\/p>\n
    SELECT ProductID, \n  Name AS ProductName,\n  StandardCost AS Wholesale,\n  ListPrice As Retail\nINTO #PriceyProducts\nFROM Production.Product\nWHERE ListPrice > 2999.99;\n\nSELECT * FROM #PriceyProducts \n<\/pre>\n
    The  SELECT<\/code><\/b> statement returns the results shown  in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductID<\/b><\/p>\n<\/td>\n
    		\n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Wholesale<\/b><\/p>\n<\/td>\n
    		\n
    Retail<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    749<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 62<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    3578.27<\/p>\n<\/td>\n<\/tr>\n
    \n
    750<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 44<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    3578.27<\/p>\n<\/td>\n<\/tr>\n
    \n
    751<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 48<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    3578.27<\/p>\n<\/td>\n<\/tr>\n
    \n
    752<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 52<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    3578.27<\/p>\n<\/td>\n<\/tr>\n
    \n
    753<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 56<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    3578.27<\/p>\n<\/td>\n<\/tr>\n
    \n
    771<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 38<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    3399.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    772<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 42<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    3399.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    773<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 44<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    3399.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    774<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 48<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    3399.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    775<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 38<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    3374.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    776<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 42<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    3374.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    777<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 44<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    3374.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    778<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 48<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    3374.99<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now suppose we want to increase the retail prices in 10% percent increments, but only as long  as the average net price remains under $2500. At the same time, the retail price must always remain under $4999.99. We  can create a WHILE<\/code><\/b>  loop that increments the price while ensuring we stay within our parameters:<\/p>\n
    DECLARE @count INT = 0\n\nWHILE (SELECT AVG(Retail - Wholesale) FROM #PriceyProducts) < 2500\n  BEGIN\n    UPDATE #PriceyProducts SET Retail = Retail * 1.1\n    SET @count = @count + 1;\n    IF (SELECT MAX(Retail) FROM #PriceyProducts) < 4999.99\n      CONTINUE;\n    ELSE\n      BREAK;\n  END;\nPRINT 'The WHILE loop ran ' + CAST(@count AS VARCHAR(10)) + ' times.'\n\n<\/pre>\n
    The  WHILE<\/code><\/b> condition checks whether the average  net price is under $2500. If the condition evaluates to  TRUE<\/code><\/b>, the statement block runs and  increases the retail prices by 10%. The statement block also includes an  IF<\/code><\/b> statement, which checks whether the  highest retail price is under $4999.00. If it is, the loop starts over. Should the  IF<\/code><\/b>  condition evaluate to FALSE<\/code><\/b>,  the query engine breaks out of the loop and jumps to the final PRINT<\/code><\/b> statement.  <\/p>\n
    I included the @count<\/code><\/b> variable only to count the  number of times the WHILE<\/code><\/b>  loop runs, which in this case, is three times, as the following results show:<\/p>\n
    The WHILE loop ran 3 times.<\/pre>\n
    If we were now to view the data in the  #PriceyProducts<\/code><\/b> table, we would see the  following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductID<\/b><\/p>\n<\/td>\n
    		\n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Wholesale<\/b><\/p>\n<\/td>\n
    		\n
    Retail<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    749<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 62<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    4762.6774<\/p>\n<\/td>\n<\/tr>\n
    \n
    750<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 44<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    4762.6774<\/p>\n<\/td>\n<\/tr>\n
    \n
    751<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 48<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    4762.6774<\/p>\n<\/td>\n<\/tr>\n
    \n
    752<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 52<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    4762.6774<\/p>\n<\/td>\n<\/tr>\n
    \n
    753<\/p>\n<\/td>\n
    		\n
    Road-150 Red, 56<\/p>\n<\/td>\n
    		\n
    2171.2942<\/p>\n<\/td>\n
    		\n
    4762.6774<\/p>\n<\/td>\n<\/tr>\n
    \n
    771<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 38<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    4525.3867<\/p>\n<\/td>\n<\/tr>\n
    \n
    772<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 42<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    4525.3867<\/p>\n<\/td>\n<\/tr>\n
    \n
    773<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 44<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    4525.3867<\/p>\n<\/td>\n<\/tr>\n
    \n
    774<\/p>\n<\/td>\n
    		\n
    Mountain-100 Silver, 48<\/p>\n<\/td>\n
    		\n
    1912.1544<\/p>\n<\/td>\n
    		\n
    4525.3867<\/p>\n<\/td>\n<\/tr>\n
    \n
    775<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 38<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    4492.1117<\/p>\n<\/td>\n<\/tr>\n
    \n
    776<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 42<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    4492.1117<\/p>\n<\/td>\n<\/tr>\n
    \n
    777<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 44<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    4492.1117<\/p>\n<\/td>\n<\/tr>\n
    \n
    778<\/p>\n<\/td>\n
    		\n
    Mountain-100 Black, 48<\/p>\n<\/td>\n
    		\n
    1898.0944<\/p>\n<\/td>\n
    		\n
    4492.1117<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    We can verify our results by running the following  SELECT<\/code><\/b>  statement to determine the current average net price:<\/p>\n
    SELECTAVG(Retail - Wholesale) FROM #PriceyProducts<\/pre>\n
    The statement returns the value  2598.9164<\/code><\/b>, which exceeds the $2500. It was  at this point that the query engine exited the  WHILE<\/code><\/b> loop. In addition, as we can see in  the results, the maximum Retail<\/code><\/b> value is  4762.6774<\/code><\/b>,  which is below the 4999.99<\/code><\/b>  limit.<\/p>\n
    “How do I add a pause or wait time to my T-SQL in  order to delay execution?”<\/h3>\n
    SQL Server supports a handy control-of-flow statement called  WAITFOR<\/code><\/b>,  which stops code execution until a specified time or for a specified amount of time. A  WAITFOR<\/code><\/b>  statement can be handy to deal with those unusual circumstances when you need to work around legacy systems or network  limitations. For example, one of your SQL Server jobs might trigger a second job that can run only at specific time  because of network constrains. <\/p>\n
    WAITFOR<\/code>  can also be used in development and testing environments to help generate specific responses. For example, suppose we’ve  created the following temporary table:<\/p>\n
    SELECT ProductID AS ProdID, \n  Name AS ProductName,\n  StandardCost AS Wholesale,\n  ListPrice As Retail\nINTO ##awproducts\nFROM Production.Product\nWHERE FinishedGoodsFlag = 1;\n<\/pre>\n
    Now suppose we want to test concurrency by running two separate operations, each in its own  session, to verify whether we’ll get a dirty read. We can start the first session, but include a  WAITFOR<\/code><\/b>  statement to delay the transaction by five seconds:<\/p>\n
    BEGIN TRANSACTION; \n \nUPDATE ##awproducts\nSET Retail = Retail * 1.2\nWHERE ProdID = 680;\n\nWAITFOR DELAY '00:00:05'  \n\nROLLBACK TRANSACTION;\n<\/pre>\n
    The  WAITFOR<\/code><\/b> statement includes the  DELAY<\/code><\/b>  keyword to indicate that we want to delay the execution, rather than use the  TIME<\/code><\/b>  option to specify a specific time. During these five seconds, we run a  SELECT<\/code><\/b> statement from our second session,  but first set the isolation level to  READ<\/code><\/b>  UNCOMMITTED<\/code><\/b>:<\/p>\n
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\n\nSELECT Retail FROM ##awproducts\nWHERE ProdID = 680;\n\t<\/pre>\n
    Because the first transaction is still active, the  SELECT<\/code><\/b>  statement returns a value of 1717.80<\/code><\/b>, the value that has been  updated in the first session. However, if we run the  SELECT<\/code><\/b> statement again, after the first  transaction completes (and rolls back), the statement returns the value  1431.50<\/code><\/b>. In other words, the first time we  ran our SELECT<\/code><\/b>  statement we received a dirty read.<\/p>\n
    We can also specify the exact time we want to end our delay by using the  TIME<\/code><\/b>  option:<\/p>\n
    DECLARE @time DATETIME = DATEADD(S, 5, GETDATE());\nWAITFOR TIME @time;\nPRINT 'The waiting is over.'; \n\t<\/pre>\n
    Once again, we’re waiting only five seconds, but you get the point. You can provide a time  rather than a range, giving you another way to control execution.<\/p>\n
    “Is it possible to skip from one section of your  T-SQL code to another?”<\/h3>\n
    In T-SQL you can use a GOTO<\/code><\/b> statement to jump to another  part of the code, as long as you’ve assigned a label to the destination. A label is merely an identifier that provides a  heading for the targeted section of code. For example, the following T-SQL includes several labeled blocks of code with  several references to them:<\/p>\n
    DECLARE @count INT = 1;\n\nIF @count = 1\n  GOTO goto_one;\nELSE IF @count = 2\n  GOTO goto_two;\nELSE IF @count = 3\n  GOTO goto_three;\nELSE\n  GOTO goto_four;\n\ngoto_one:\nPRINT 'This is goto_one.'\nGOTO goto_four;\n\ngoto_two:\nPRINT 'This is goto_two.'\nGOTO goto_four;\n\ngoto_three:\nPRINT 'This is goto_three.'\nGOTO goto_four;\n\ngoto_four:\nRETURN\n\n<\/pre>\n
    When you reference a label, you provide only the label name along with the  GOTO<\/code><\/b>  keyword. However, when you assign a label to a bock of code, you must also include a colon. When the query engine  encounters a GOTO<\/code><\/b> statement, it jumps to the  label identified in the statement. For example, the initial  IF<\/code><\/b> statement includes a  GOTO<\/code><\/b>  statement that points to the  goto_one<\/code><\/b> label. As a result, if the  @count<\/code><\/b>  variable has a value of 1<\/code><\/b>,  the query engine will jump to the  goto_one<\/code><\/b> label, execute the  PRINT<\/code><\/b>  statement associated with that label, and return the following results.<\/p>\n
    This is goto_one.<\/pre>\n
    Of course, you’re likely to want to use the  GOTO<\/code><\/b> statement for more practical purposes  than in the example above. For example, suppose your T-SQL includes a number of  IF<\/code><\/b>  statements that take different steps. Regardless of which condition evaluates to  TRUE<\/code><\/b>,  you want to run a final  INSERT<\/code><\/b> statement that logs the event. You can do something similar to the following:<\/p>\n
    CREATE TABLE #UsageLog\n(LogID INT IDENTITY, Usage VARCHAR(20), \n  LogTime DATETIME DEFAULT GETDATE());\n\nDECLARE @CustID INT = 170, @usage VARCHAR(20) = '';\n\nIF @CustID IS NULL\n  BEGIN\n    PRINT 'You must provide a customer ID.';\n    PRINT 'Contact the sales rep for your region:';\n    SELECT FirstName, LastName, TerritoryName, EmailAddress\n    FROM Sales.vSalesPerson\n    WHERE JobTitle = 'Sales Representative';\n    SET @usage = 'ID null';\n    GOTO log_usage;\n  END\nELSE IF EXISTS(SELECT * FROM Sales.vIndividualCustomer\n    WHERE BusinessEntityID = @CustID)\n  BEGIN\n    SELECT FirstName, LastName, City, StateProvinceName\n    FROM Sales.vIndividualCustomer\n    WHERE BusinessEntityID = @CustID;\n    SET @usage = 'ID valid';\n    GOTO log_usage;\n  END\nELSE\n  BEGIN\n    PRINT 'No record matches the customer ID ' + \n      CAST(@CustID AS VARCHAR(10)) + '.'\n    SET @usage = 'ID invalid';\n    GOTO log_usage;\n  END\n\nlog_usage:\nINSERT INTO #UsageLog (Usage) VALUES(@usage);\n<\/pre>\n
    For each possible condition, the statement block ends with a  GOTO<\/code><\/b>  statement that points to the  log_usage<\/code><\/b> label, so no matter what happens  elsewhere, the last statement here will run. Although we’ve included only a simple  INSERT<\/code><\/b>  statement with out label, you can use this strategy to avoid having to recode more complex logic in multiple places.<\/p>\n
    “What’s the simplest way to incorporate error  handling into my stored procedures?”<\/h3>\n
    Since the release SQL Server 2005, error handling has been fairly easy to incorporate in your  T-SQL code through the use of  TRY...CATCH<\/code><\/b> blocks. The idea behind this is  that you put the main body of your code in the  TRY<\/code><\/b> block and put the error handling in the CATCH<\/code><\/b>  block. The statements in the  CATCH<\/code><\/b> block will run only if the query  engine encounters an error in the  TRY<\/code><\/b> block.<\/p>\n
    When Microsoft first added support for  TRY...CATCH<\/code><\/b> error handling, you had to use a RAISERROR<\/code><\/b> statement to catch and  throw the error, should one occur. But that changed in SQL Server 2012. You can now use a  THROW<\/code><\/b> statement, which is simpler  to use and more accurate.<\/p>\n
    To demonstrate how all this works, let’s start with a simple temporary table and add a few  rows of data:<\/p>\n
    CREATE TABLE #products\n(ProdID INT PRIMARY KEY, ListPrice MONEY);\n\nINSERT INTO #products VALUES\n(101, 199.99),\n(102, 299.99),\n(103, 399.99);\n<\/pre>\n
    Now let’s create a stored procedure that adds a row to the table, with the values coming from  two input parameters:<\/p>\n
    IF OBJECT_ID('dbo.AddProduct', 'p') IS NOT NULL\nDROP PROCEDURE dbo.AddProduct;\nGO\n\nCREATE PROCEDURE dbo.AddProduct\n  (@ProdID INT, @ListPrice MONEY)\nAS\nBEGIN TRY\n  BEGIN TRANSACTION;\n    INSERT INTO #products VALUES (@ProdID, @ListPrice);\n  COMMIT TRANSACTION;\nEND TRY\nBEGIN CATCH\n  IF @@TRANCOUNT > 0\n    ROLLBACK TRANSACTION;\n  PRINT 'The stored procedure generated the following error:';\n  THROW;\nEND CATCH;\nGO\n<\/pre>\n
    First, notice that the procedure definition includes the  TRY...CATCH<\/code><\/b>  blocks. In the TRY<\/code><\/b>  block, we include the primary T-SQL needed to begin the transaction, insert the vales into the table, and commit the  transaction. If the statements in the  TRY<\/code><\/b> block run with no problem, the  operation completes and the database engine exists the  TRY...CATCH<\/code><\/b> blocks. However, if the  TRY<\/code><\/b>  block generates an error, the database engine skips to the CATCH<\/code><\/b> block where the error can be  handled.<\/p>\n
    In this example, the CATCH<\/code><\/b> block includes three  statements. The first is an  IF<\/code><\/b> statement that rolls back the  transaction if the  @@TRANCOUNT<\/code><\/b> system variable value is greater than  0<\/code><\/b>, which means a transaction is  active in the current session. Next, it includes a  PRINT<\/code><\/b> statement, which I’ve included  primarily to verify that the CATCH<\/code><\/b> block statements are being  executed should an error occur in the  TRY<\/code><\/b> block. Finally, I add a  THROW<\/code><\/b>  statement to catch and return the error.<\/p>\n
    Let’s look at the stored procedure in action to demonstrate how everything works. First,  we’ll use the procedure to add a row to our temporary table and then run a  SELECT<\/code><\/b> statement:<\/p>\n
    EXEC AddProduct 104, 499.99;\n\nSELECT * FROM #products;\n\t<\/pre>\n
    Not surprisingly, the query returns four rows, including a row for product 104, as shown in  the following results:<\/p>\n\n\n\n\n\n\n\n
    \n
    ProdID<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    101<\/p>\n<\/td>\n
    		\n
    199.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    102<\/p>\n<\/td>\n
    		\n
    299.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    103<\/p>\n<\/td>\n
    		\n
    399.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    104<\/p>\n<\/td>\n
    		\n
    499.99<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s try to run the same  EXECUTE<\/code><\/b> command again:<\/p>\n
    EXEC AddProduct 104, 499.99;<\/pre>\n
    Because we’d be violating the primary key by adding this row, the query engine baulks and  returns the following results:<\/p>\n
    The  stored procedure generated the following error:<\/p>\n
    Msg 2627, Level 14, State 1, Procedure AddProduct, Line 1058\nViolation of PRIMARY KEY constraint 'PK__#product__042785C5164AE135'. Cannot insert duplicate key in object 'dbo.#products'. The duplicate key value is (104).\n\n<\/pre>\n
    Notice that the results include the message from the  PRINT<\/code><\/b>  command, along with the error details provided by the  THROW<\/code><\/b> statement. <\/p>\n
    The  THROW<\/code><\/b> statement also let’s you define your  own error number, message, and state, as shown in the following example:<\/p>\n
    IF OBJECT_ID('dbo.AddProduct', 'p') IS NOT NULL\nDROP PROCEDURE dbo.AddProduct;\nGO\n\nCREATE PROCEDURE dbo.AddProduct\n  (@ProdID INT, @ListPrice MONEY)\nAS\nBEGIN TRY\n  BEGIN TRANSACTION;\n    INSERT INTO #products VALUES (@ProdID, @ListPrice);\n  COMMIT TRANSACTION;\nEND TRY\nBEGIN CATCH\n  IF @@TRANCOUNT > 0\n    ROLLBACK TRANSACTION;\n  PRINT 'The stored procedure generated the following error:';\n  THROW 55555, 'SQL Server threw an exception because of the way you called your stored procedure.', 1;\nEND CATCH;\nGO\n\n<\/pre>\n
    If we were to once again try to add product 104, out results would now look like the  following:<\/p>\n
    The stored procedure generated the following error:\nMsg 55555, Level 16, State 1, Procedure AddProduct, Line 1111\nSQL Server threw an exception because of the way you called your stored procedure.\n\n<\/pre>\n
    It’s up to you whether to go with the generic message or create your own. The  THROW<\/code><\/b>  statement on its own is a powerful tool, and that might be all you need. There is more to error handling, though, so you  might benefit from an article I wrote a while back on the subject: “Handling  Errors in SQL Server 2012<\/a>.”<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    Surely, we all know how T-SQL Control-of-flow language works? In fact it is surprisingly easy to get caught out. What, for example,  do the BREAK, RETURN and CONTINUE  keywords do, precisely,  in their various contexts? the answers to this  and other questions aren’t all entirely obvious, but we’re too shy to ask them in public, and risk displaying our ignorance.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4179,4150,4183,4252,5771],"coauthors":[],"class_list":["post-1941","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-source-control","tag-sql","tag-t-sql","tag-t-sql-programming","tag-too-shy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1941","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1941"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1941\/revisions"}],"predecessor-version":[{"id":54525,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1941\/revisions\/54525"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1941"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}