Week 3

Day 18
Advanced Stored Procedure Issues

Yesterday we looked at some broad performance topics for stored procedure programming. Today we will look at some specific coding issues and a couple of special topics.

Today’s lesson covers the following topics:

•  Procedures and INSERT

•  Procedures and transactions

•  Error handling and procedures

•  System procedures

•  Remote procedures

Procedures and INSERT

Back on Day 9, you learned how to insert rows from the results of a SELECT statement. For example, this statement would add rows to the working_publishers table from the publishers table:

insert   working_publishers   (pub_id, pub_name)
select   pub_id,  pub_name
from     publishers

Another new feature in SQL Server 6.5 allows you to use a stored procedure as the source of records for an INSERT statement. Here is a stored procedure that performs the same selection from the publishers table:

create   procedure   pub_select
a s
select   pub_id,     pub_name
from     publishers
return

Now you can use the INSERT … EXECUTE syntax to insert the rows selected:

insert     working_publishers   (pub_id, pub_name)
execute    pub_select

SQL Server executes the procedure and then inserts the contents of the procedure result set into the working_publishers table. Notice that, if the result set from the procedure does not match the set of columns expected by the INSERT statement (both in number of columns and in respective data types), the statement fails at runtime.

Transaction and INSERT … EXECUTE

Let’s look more closely at this problem because it could pose a problem in transactional integrity. On Day 13, you learned about transactions. One of the things you learned was, every individual SQL statement is a transaction. In the INSERT … SELECT example you just saw, the INSERT statement is a transaction: it will complete successfully or it will fail. Let’s test that assertion with an execution that returns two result sets, one that matches the requirements of the INSERT statement and another that does not. Create an empty copy of the publishers table in tempdb:

/*  execute  from  tempdb  database  */
select   pub_id,  pub_name
into     tempdb..my_publishers
from     pubs..publishers
where 1 = 2

Now we’ll insert into that publishers table using an on-the-fly execution example. (This operates the same way as a stored procedure example and is easier to set up.) The on-the-fly example will include two result sets, one that matches the insert requirements, the other that does not. Finally, we’ll retrieve the contents of the temporary table.

set  nocount  on
declare  @to_execute   varchar(255)
select   @to_execute  =  "print ’starting execution’ "
select   @to_execute  =  @to_execute
                   + "select pub_id, pub_name from pubs..publishers "
select   @to_execute  =  @to_execute + "select count(*) from
pubs..publishers"
insert   tempdb..my_publishers  (pub_id, pub_name)
execute     (@to_execute)
select  *  from  tempdb..my_publishers

starting   execution
Msg 213, Level 16, State 1
Insert  error:  column  name  or  number  of  supplied  values
does  not  match  table  definition.
Command  has  been  aborted.
pub_id  pub_name
------                ------------------------
0736   New Moon Books
0877   Binnet & Hardley
1389   Algodata Infosystems
1622   Five Lakes Publishing
1756   Ramona Publishers
9901   GGG&G
9952   Scootney Books
9999   Lucerne Publishing

We receive two messages. The first is our PRINT message, indicating that execution is starting. (Notice that PRINT statements and other quiet operations do not disturb the insertion of data from a procedure result set.) The second message is from SQL Server alerting us that the INSERT operation encountered an error. The second result set contained only one column where the INSERT expected two.

The server warns that the command has been aborted, but look at the contents of the table at the end. The eight rows in the table were added by the first half of the INSERT statement. The second half of the statement failed, but it did not roll back the first half.

---

WARNING:  The INSERT … EXECUTE statement is not protected by the normal transaction behavior of all other SQL statements. To guarantee transactional integrity through the INSERT operation, use transaction control mechanisms:

begin  tran
       insert  tempdb..my_publishers  (pub_id, pub_name)
       execute  (@to_execute)
       if @@error != 0
          begin
            rollback tran
          return
       end
commit tran

Transaction control allows you to back out the first operation (ROLLBACK TRANSACTION) if the second operation fails.

---