Home > IT > Programming > SQL > iDog's Quick Guide to Sybase Stored Procedure

iDog's Quick Guide to Sybase Stored Procedure

Tutorial

Connect to database (interactive mode):


isql -Sdb_server -Uuser -Ppassword -D db_name

Connect to database, execute some queries and finish:


isql -Sdb_server -Uuser -Ppassword  -D db_name << END_OF_CMD

<sql or calling stored procedure>
go

END_OF_CMD

Note: Before 'go' and 'END_OF_CMD' above, no space is allowed.

Check if stored procedure exists


select 1 from sysobjects where name = 'insert_old_future_instrument' and type = 'P'

Create stored procedure


create procedure my_proc
   @param1 TYPE1
   @param2 TYPE2
as
   ...
   
go

Parameter list:


@param_name TYPE [, <more declarations>]

Declare local variable


declare @var_name TYPE

Assign query result to variable


select @my_var = MyField from MyTable where MyKey = 0

Check number of results returned from query


@@rowcount

Condition


if (<condition>)
   <do something in one line>
else
   <do something else in one line>

if (<condition>)
begin
   <do something more in multiple lines>
end

Examples

Example 1

Check if stored procedure exists, if not, create it; if exists, skip. Make it as a shell script.


createStoredProc() {
    myProcExistIndicator='MY_PROC_EXIST_INDICATOR'
    isProcExist=`isql -Sdb_server -Uuser -Ppwd -D db_name << END_OF_CHECK_PROC
select "$myProcExistIndicator" from sysobjects where name = 'my_proc' and type = 'P'
go
END_OF_CHECK_PROC`

    isProcExist=`ec ho $isProcExist | grep $myProcExistIndicator`

    if test "x$isProcExist" != "x" ; then    # already exists
        return
    fi

    isProcExist=`isql -Sdb_server -Uuser -Ppwd -D db_name << END_OF_CREATE_PROC
create procedure my_proc
    @param1 TYPE1,
    @param2 TYPE2
as
   <body of the stored procedure>
go

END_OF_CREATE_PROC

}

Example 2

Two tables:

articles

title (key)
contents
author_id (foreign key, id of persons table)

persons

id (key)
name
phone
address

Given data (title, contents, name, phone, address), if article exists in table, then skip it; otherwise insert it in table. Also check if person in table, if not, insert it.


create procedure my_insert_proc
   @title      TITLE,
   @contents   CONTENTS,
   @author      NAME,
   @phone      PHONE,
   @address   ADDR
as
   /* skip if it exists */
   if (select count(*) from articles where title = @title) > 0
   begin
      print "@title is already in DB. Skip it."
      return
   end
   
   declare @id ID
   
   /* search for the person */
   select @id = id from persons
   where name  = @name
   and   phone = @phone
   
   if(@@rowcount = 0)
   begin
      select @id = (max(id) + 1) from persons
      
      /* it can be null if it's the first one */
      if(@id is null)
      begin
         select @id = 0
      end
      
      insert into persons values (
         @id,
         @name,
         @phone,
         @address
      )
   end
   
   insert into articles values (
      @title,
      @contents,
      @id
   )
go

Top Page

Links