Listing 1 - Stored Procedure Source for Sybase ASE

// Controls - stored procedures for select all, select one, insert, update, delete
create procedure get_bsk_controls_list
as
	select 	company_id,
		name,
		address1,
		address2,
		city,
		state,
		zip,
		email_address,
		phone,
		contact
	from	bsk_controls

create procedure get_bsk_controls
@company_id char(10)
as
	select	company_id,
		name,
		address1,
		address2,
		city,
		state,
		zip,
		email_address,
		phone,
		contact
	from	bsk_controls
	where	company_id = @company_id

create procedure insert_bsk_controls
@company_id char(10),
@name char(30),
@address1 char(30),
@address2 char(30),
@city char(15),
@state char(2),
@zip char(5),
@email_address char(50),
@phone char(10),
@contact char(30)
as
	insert	bsk_controls (
		company_id,
		name,
		address1,
		address2,
		city,
		state,
		zip,
		email_address,
		phone,
		contact)
	values	(
		@company_id,
		@name,
		@address1,
		@address2,
		@city,
		@state,
		@zip,
		@email_address,
		@phone,
		@contact)

create procedure update_bsk_controls
@company_id char(10),
@name char(30),
@address1 char(30),
@address2 char(30),
@city char(15),
@state char(2),
@zip char(5),
@email_address char(50),
@phone char(10),
@contact char(30)
as
	update	bsk_controls
	set	name = @name,
		address1 = @address1,
		address2 = @address2,
		city = @city,
		state = @state,
		zip = @zip,
		email_address = @email_address,
		phone = @phone,
		contact = @contact
	where 	company_id = @company_id

create procedure delete_bsk_controls
@company_id char(10)
as
	delete	bsk_controls
	where	company_id = @company_id


// Menus - stored procedures for insert, update, delete
create procedure insert_bsk_menus
@menuid char(10),
@description char(30)
as
	insert	bsk_menus (
		menuid,
		description)
	values (
		@menuid,
		@description)

create procedure update_bsk_menus
@menuid char(10),
@description char(30)
as
	update	bsk_menus
	set	description = @description
	where	menuid = @menuid

create procedure delete_bsk_menus
@menuid char(10)
as
	delete	bsk_menus
	where	menuid = @menuid


// Messages - stored procedures for select all and select one
create procedure select_bsk_messages_list
as
	select	id,
		subject,
		message
	from	bsk_messages

create procedure select_bsk_messages
@id integer
as
	select	id,
		subject,
		message
	from	bsk_messages
	where	id = @id


// Users - stored procedures for select all, select one, and update
create procedure select_bsk_users_list
as
	select	userid,
		fullname,
		permission_control,
		permission_users,
		permission_menus,
		permission_messages,
		permission_contacts
	from	bsk_users

create procedure select_bsk_users
@userid char(20)
as
	select	userid,
		fullname,
		permission_control,
		permission_users,
		permission_menus,
		permission_messages,
		permission_contacts
	from	bsk_users
	where	userid = @userid

create procedure update_bsk_users
/* we have to pass the original value so we can find the record to update in case 
the userid value was changes during editing by the user
*/
@userid_orig char(20),
@userid char(20),
@fullname char(30),
@permission_control char(1),
@permission_users char(1),
@permission_menus char(1),
@permission_messages char(1),
@permission_contact char(1)
as
	update	bsk_users
	set	userid = @userid,
		fullname = @fullname,
		permission_control = @permission_control,
		permission_users = @permission_users,
		permission_menus = @permission_menus,
		permission_messages = @permission_messages,
		permission_contact = @permission_contact
	where	userid = @userid_orig