tablename_startnnn stored procedure

tablename_startnnn is used to START a file. The nnn value is the key number to start on, and is 0 filled. For example, the start procedure for the primary key for table mytab is "mytab_start000".

Because there can be up to 119 alternate keys, the Connector does not search for a start procedure unless, or until, it is used. The parameters passed to the stored procedure are a 2-char mode [it is a varchar(2) field], with one of the following values: ">", ">=", "=", "<=", or "<". The rest of the parameters are the columns of the key used to start. Because the ANSI specification for START includes information about the size of the key being used (and in particular allows partial keys), the start procedure is used only if an entire key is given to the start verb. This procedure is also special in that it does not return data, but needs to raise an error condition if the start fails. The way to raise the error condition from within the stored procedure is to include code similar to the following:

raiserror (523409,16,1 "Record not found")

The code 523409 is very important. It is the code searched for in setting the error condition from within Database Connectors. If you use a different number, your starts may succeed when they should actually fail.

For example, based on the Sample XFD, you can create the following stored procedure to start a file:

create procedure ftestdat_start001
@mode varchar(2),
@ft2_key1_seg1 char(2),
@ft2_key1_seg2 char(2)
as

if exists (select 1 from ftestdat where

(ftest2_key1_seg1 = @ft2_key1_seg1 and
((@mode = ">=" and ftest2_key1_seg2 >=@ft2_key1_seg2) or
(@mode = ">" and ftest2_key1_seg2 > @ft2_key1_seg2) or
(@mode = "=" and ftest2_key1_seg2 = @ft2_key1_seg2) or
(@mode = "<" and ftest2_key1_seg2 < @ft2_key1_seg2) or
(@mode = "<=" and ftest2_key1_seg2 <= @ft2_key1_seg2))))
return
if exists (select 1 from ftestdat where
(((@mode = ">=" or @mode = ">") and 
ftest2_key1_seg1 > @ft2_key1_seg1) or
((@mode = "<=" or @mode = "<") and
ftest2_key1_seg1 < @ft2_key1_seg1)))
return
raiserror (523409,16,1 "Record not found")

grant execute on ftestdat_start001 to public

This table shows the codes that can be used when setting the error condition from within Database Connectors.