Who Am I?

This example tells me what locks I currently have open. If you call sp_lock and pass it a process ID, the procedure will report the locks open for that process. Locking is covered tomorrow, with transactions.

execute sp_lock @@spid

spid   locktype  table_id    page        dbname
------ --------- ----------- ----------- ------
11     Sh_intent 688005482   0           master
11     Ex_extent 0           376         tempdb

PRINT

The PRINT statement is used to return a message to the client. SELECT returns data to the client in the form of a result set. Client programs must handle result sets by stepping through each row in the result set. PRINT statements are handled by a client’s message handler (in DB-Library). In Visual Basic, if you are using RDO, a PRINT statement is routed to the SQLErrors object collection. This makes PRINT statements a little easier to handle.

PRINT is used to return either informational messages, such as “New primary key added,” or error messages, such as, “You must provide a value for the quantity parameter.” These types of statements are most often issued from a stored procedure.

Simply pass a string to the PRINT statement:

print “Free Andora Now!”

Free Andora Now!

If you want to put several strings together, prepare them in a local variable first:

declare @msg varchar(255)

select @msg = “The last error number to occur was “
select @msg = @msg + convert(varchar(5), @@error)
print @msg

The last error number to occur was 0

The PRINT statement will not concatenate strings. You must concatenate them into a variable before printing.

Flow Control

ANSI SQL and T-SQL provide three flow control statements.

•  IF, for conditional execution

•  WHILE, for looping

•  RETURN, for unconditional exit from a batch or procedure

IF

Here is a simple example that tests the value of a local variable and prints a different message depending on the value of that variable:

declare @price money

select @price = price from titles
where  title_id = ‘BU1111’

if @price < $10
      print ‘Under $10’
else
      print ‘Equal to or Over $10’

Equal to or Over $10

The IF test is composed of three parts. Immediately following the IF keyword is the boolean test. If the expression following the IF is true, the statement following the test is executed. If the expression following the IF is false, the statement following the ELSE is executed. The ELSE clause is optional: If one is not specified and the IF test is false, processing continues by skipping the statement after the IF test.

Logical Shortcutting in the IF Statement

Logical shortcutting is an operation involving IF statements that test multiple AND conditions in structured languages. (You may have heard of this as “short circuit Boolean logic.”) Some languages use shortcutting to save time: if the first part of the AND is false, the whole statement is false, so there is no need to test the later conditions.

if ((@price > $25) and (@type = business))

If the server used logical shortcutting, and the price were $18, it wouldn’t bother testing the type.

SQL Server does not use logical shortcutting. You can use subqueries in your IF statements, and rest assured that all queries will be performed, even if the first one makes the IF test false. Likewise, if one part of an OR clause is true, SQL Server will still evaluate the other clauses in the OR.

BEGIN and END

You might have noticed that IF allows for just a single statement to be executed after the test. “One statement ain’t much,” I can hear you saying, and you’re right. The BEGIN and END keywords demarcate a block of code that can be placed after the IF test or inside a WHILE loop.

In this example, the error variable is tested, and if it is not zero, the batch is aborted, the transaction is rolled back, and an entry is made in an errors table:

begin   transaction
update  inventory
set     qty = qty + 5
where   item_id = 31415

select @err = @@error
if @err <> 0 begin
      rollback transaction
      insert upd_errors (err_time, errnum)
      values (CURRENT_TIMESTAMP, @err)
      return
end

The @@error variable is placed into a local @err variable. This saves the @@error status. This is tested, and if there was an error with the inventory update, several things occur: the transaction is rolled back, an insert is made to the upd_errors table, and the batch is aborted with RETURN. There’s a lot of stuff here that I haven’t covered, but hold on and I’ll get to everything.

---

Note:  Style Police: BEGIN and END
There are lots of ways to format BEGIN and END code blocks. I’m an old C programmer, so I tend to cling to C-oriented syntax. I also think it’s prettiest. BEGIN and END serve the same function as C’s curly braces.

if @@error <> 0 begin
    indented statement
    indented statement
end

Some SQL programmers prefer to place BEGIN and END on separate lines, together with the indented statements:

if @@error <> 0
    begin
      indented statement
      indented statement
    end

Less commonly, some folks prefer to flush left the BEGIN END keywords:

if @@error <> 0
begin
    indented statement
    indented statement
end

Do not use BEGIN and END frivolously. Use them only when you intend to group blocks of code after an IF or WHILE. Some programmers use BEGIN END inside stored procs to contain the entire procedure. This is not necessary and is a stylistic mistake.

Also, if you are executing a single command, do not include it between BEGIN and END statements.

---

To summarize, BEGIN and END allow you to perform more than one statement inside a WHILE loop or after an IF test.