Sybase Programming Basics

/* -----------------------------------
   Main run
   --------
   initialise the input feed tables
   -------------------------------- */
delete from feed_master where runresult < 0
exec initialisefeedproc
go
-- --------------
-- start the feed
-- --------------
exec runfeedproc
go
-- ------------------
-- check the feed ran
-- ------------------
select feedresult from feedmaster where runresult < 0
go

Note that there's no error handling evident in this script other than at the end. I leave it as an excercise for the reader to decide if and what error handling may be appropriate.

Comments.

Sybase allows both the multi-line (/* comment */) and single line (-- comment) forms, in common with most modern versions of SQL, as shown in the above sample.

Local Variables.

Sybase has both local and global variables but only permits the user to define local variables, which must always start with '@'. Local variables exist only for the life of the current batch. This is important, too many people spend time looking for bugs caused by assuming that variables will span an entire script.

Local variables are declared and then loaded with a select statement, as in the following example...

-- ----------------------
-- Create the variable...
-- ----------------------
declare @NewSalesman varchar(40)

-- ----------------------------
-- then load it from a table...
-- ----------------------------
select @NewSalesman = Salesman
from update
where NextProcess = 'update'
go

-- ---------------------------------------
-- ...a waste of time because @NewSalesman
-- has now ceased to exist!
-- ---------------------------------------

Note that you can't use the same query both to retrieve data and set a variable. You may also load a variable with a constant value as in...

-- -----------------
-- Create test value
-- -----------------
declare @TestValue
select @TestValue = 'A345321'
etc....

Global Variables.

These start with '@@' and are read only. There's a fairly long list of which some of the more useful are...

@@rowcount    The number of rows returned by the last query.
              Everything (except declare) changes this so store it
              in an int if you'll need it later.
@@error       The last error number raised. More or less the same
              applies to this as to @@rowcount.
@@trancount   How deep the transaction nest is currently. If you have
              any uncommitted transactions, this will be greater than
              zero. Conversely, if it's zero, there are no outstanding
              transactions.
@@servername The name of the server the query is being run against.
              Commonly used by DBAs, this can also be usefull to stop
              code being run against the wrong database.
@@version     The server and O/S version numbers.
@@spid        The ID of the current process.
@@identity    The last identity value used by an insert.
@@nestlevel   How many levels down the current procedure or trigger is.
@@sqlstatus   The result of the last fetch on a cursor.

The Print Command.

This is very simplistic. Basically, it just passes a message to the client programme's message handler. Messages are limited to 255 characters and must be either a single string or a single variable...

print 'Number of orders in transaction exceeds number on file'
print @SalesOrderErrorMessage

Raising Errors.

If the default error messages are insufficient for your needs, Sybase allows you to create your own with the raiserror command. Sybase Errors are identified by a number in the range 50,001 to 2,147,483,647 so the chances of running out of codes are fairly remote. Associated with each error number is a severity level, state and message.

Error messages need to be stored in sysmessages before use as in...

sp_admessage 57001, 16, "Customer code %s is currently on credit hold - not updated"

The %s in the above example is a placeholder, similar to the variables in the Unix printf command. If a suitable value is supplied when the error is raised, it will replace the %s.

You can either raise an existing error or you can generate one of your own, as in the following example.

raiserror(57001, 16, 1, @CustCode)

will result in the following message being returned to the client...

MSG 57001, Level 16, State 1
Customer Code GHB31987 is currently on credit hold - not updated

if..else

This can be used anywhere in a batch and works pretty well as you might expect. The following example shows how you can compare the result of a query to a constant. Note that a query must be enclosed in brackets...

if (select max(invoice)
      from sales
   where office = 'Swindon') > 2000.00
   @SwindonCovered = 'Yes'
else
   @SwindonCovered = 'No'

and this illustrates the use of the exists test...

if exists (select CustCode
from Customer
where Status = 'Live'
and Salesman is null)
print 'Orphaned customer found - run orphans report before month end'

Sybase permits you to nest if statements up to 150 levels, which should be enough for most purposes! Note also that only one statement or block is triggered by if and the same for else, which brings us neatly onto...

Statement Blocks.

We can alter the example above to look something more like the real world by doing the following...

if exists (select CustCode
   from Customer
where Status = 'Live'
and Salesman is null)
   begin
    print 'Orphaned customer found - running orphans report...'
      exec orphans_report
      exec clear_orphans
   end

begin and end may enclose any number of statements.

The Case Statement.

Sybase also supports a case statement which is clearer in use than Oracle's decode and can, usefully, also support code blocks...

case @Office
   when 'Swindon' then exec SwindonMonthEnd
   when 'Slough' then exec SloughMonthEnd
   when 'Devizes' then exec DevizesMonthEnd
   else exec OfficeNotRecognised @Office

Coalesce

We just mentioned the Oracle decode statement and here's another take on the same process...

select @Office = coalesce(@MainOffice, @BranchOffice, @RepAlone)

This will select the first item in the list which is not NULL. All succeeding values are ignored. If all the values in the list are NULL, coalesce returns a NULL.

While Loops.

One of the nicest features in any language is the ability to continue an activity only so long as a particular condition is true, Transact SQL provides the while construct for this purpose. The following example shows the construct in use...

-- ---------------------------------------------------
-- This loop keeps going so long as it can find a sale
-- for the current office. UpdateSales removes each
-- transaction from SalesList as it processes it and
-- sets up the next one to be processed at the same
-- time...
-- ---------------------------------------------------
while (select Office
         from SalesList
where ProcessThis = 'yes') = @ThisOffice
   begin
      exec UpdateSales @ThisOffice
      if (select ProductType
from SalesLedger
           where Office = @ThisOffice
             and Posted = @ThisDate) != 'SERVICE ONLY'
         -- ----------------------------------------
         -- Ordinary sale, so processing finished
   -- Go back to beginning and do next sale...
         -- ----------------------------------------
         continue
      else
         -- ----------------------------------
         -- This is a non-product transaction.
         -- Set up the timesheet charging...
         -- ----------------------------------
         exec BuildTimesheet @ThisOffice
         if (select Status
               from TimeSheetControl
              where Office = @ThisOffice) = 'DUPLICATE'
             -- -----------------------------------------
             -- Oops! That shouldn't occur in real life
             -- Report the problem and quit processing...
             -- -----------------------------------------
             raiseerror(51097, 16, 1, @ThisOffice)
             break
   end

This is an idealised example of while. The condition attached to the while is evaluated and if TRUE, the code block following is executed. During the execution of the code block, a further test decides if further processing within the block should be ignored. If that is the case, continue is invoked, which restarts processing at the top of the loop with the while statement itself. If processing continues, the code then tests for an error condition and if this is detected, invokes break. The break command falls out of the loop and continues execution at the next statement which is not part of the loop's block, in this case, the first statement after end.

Go To, Thou...

Transact SQL supports a goto command which, the screams of structured programming purists apart, can be extremely useful for solving certain types of problem. To use goto, you need to define labels and, when invoked, goto will pass execution to the next statement following the label. Labels may be any string followed by a colon (:).

You need to be careful how you use goto because there are certain types of backwards references that Sybase cannot resolve. For this reason, use goto only as a weapon of last resort when no other programming construct will do and test, test, test to make sure it works as you expect...

LoadSalesTableStart:
   -- -------------------------------------------------
   -- If an error is detected, this will loop
   -- continuously unless interrupted by an operator...
   -- -------------------------------------------------
   exec LoadSalesTable
   exec UpdateSalesman
   exec WriteInvoices
   if (select status
         from RunControl
        where RunID = @ThisRun) != 'Success'
      begin
         raiserror(58932, 16, 1)
         goto LoadSalesTableStart
      end

The above example may seem a bit contrived but we've actually done it in real life when dealing with feeds that may not arrive exactly on time but without which further processing cannot continue.

Waiting for...

The Sybase waitfor command is one of those useful little facilities that no-one knows how to use unless they've got a problem only it can solve. The waitfor command operates, effectively, in two different ways. The first is as a simple timer. You give it either a length of delay or a time of day and Sybase will hold your process until the sepecified delay is over or the specified time of day is reached.

The other way it works is as an event handler. Used this way, you give waitfor an event to watch for and when the event occurs, execution will resume. The events you can wait for are errorexit (a process terminates abnormally) processexit (a process terminates for any reason) and mirrorexit (a read or write to a mirrored device fails)

For obvious reasons, the second use of waitfor is largely confined to DBAs who want to check for problems on the system. We've never yet found a reason to use waitfor in this way ourselves. The first version, though, is extremely useful for dealing with feeds from remote systems as in the following alternative version of the previous example...

-- ------------------------------------------------------------
-- Don't start this until 2:30PM to allow for feed to arrive...
-- ------------------------------------------------------------
waitfor time "14:30:00"

-- --------------------------
-- Feed should be here now...
-- --------------------------
LoadSalesTableStart:
   -- -------------------------------------------------
   -- If an error is detected, this will loop
   -- continuously unless interrupted by an operator...
   -- -------------------------------------------------
   exec LoadSalesTable
   exec UpdateSalesman
   exec WriteInvoices
   if (select status
         from RunControl
        where RunID = @ThisRun) != 'Success'
      begin
         -- ------------------------------------
         -- Probably the feed's late - log it...
         -- ------------------------------------
         raiserror(58932, 16, 1)

         -- ------------------------------------
         -- Give it 1/2 an hour and try again...
         -- ------------------------------------
         waitfor delay "00:30:00"
         goto LoadSalesTableStart
      end

Note that waitfor will ONLY accept constants, as shown in the example, and not variables. The way round it is shown in the next section.

Executive decisions...

The execute command takes any string and passes it to Sybase as a command. This allows you to effectively create programmes on the fly and to get around limitations in Sybase such as waitfor's inability to accept variables as arguments. Here's a rather contrived example...

declare @Command char(255),
        @CurrentTime datetime,
        @Hour tinyint
select @CurrentTime = getdate(),
       @Hour = datepart(hh, getdate())
select @Command = "waitfor time '" + str(@Hour) + ":45:00'"
execute @Command

Time to Return...

Finally, we come to the return command which terminates the current batch and optionally gives a value to the calling routine.

begin
   -- --------------------------------------------------
   -- If an error is detected, fall out straight away...
   -- --------------------------------------------------
   exec LoadSalesTable
   if (select status
         from RunControl
        where RunID = @ThisRun) != 'Success'
      return
   exec UpdateSalesman
   exec WriteInvoices
end

Sybase offers a surprisingly powerful set of programming tools within the confines of Transact SQL. It is worth exploring them before turning to additional and more complex programming systems.