When we create stored procedures etc here we run the through
the C pre-processor which allows us to do #define's (see below)

It allows us to do the drop_proc and grant_proc etc etc style code 
in the example at the bottom.

The first section is the #defines for the things we want expanded.

If the syntax ever changes we can just change this header file

It has its pro's and cons. I guess we could use m4 as that is likely
to be present on a users (unix) system.

Steve
si@plexstar.com (Steve Ingram)


******************************************************************************/

/*.file_hdr
*******************************************************************************

    File:  $RCSfile: sqldefs.h,v $

    Purpose:
	General SQL constants and macros.

******************************************************************************/

/*
$Id: sqldefs.h,v 1.1 1996/06/12 19:48:56 si Exp $
*/



#ifndef _SQL_DEFS_H
#define _SQL_DEFS_H



/*  boolean constants  */

#define FALSE 0
#define TRUE 1



/*  Highest error number reserved for Sybase SQL database server error
    messages.  */

#define MAX_SYBASE_ERROR 20000

/*  This is the Sybase error ID corresponding to an attempt to insert a
    duplicate key row into a table with a unique index.  This constant is used
    to test for this error so that the associated Sybase message can be
    followed with one that is more meaningful to the user, e.g., "duplicate
    document name, object ID, etc.".  */

#define SYB_ERR_DUP_KEY 2601



/*  This macro drops an object referenced through the "sysobjects" table.  */
#define drop_object(object_name, type_name, type_id) \
if exists ( \
    select * from sysobjects \
    where \
	name = "object_name" \
	and type = "type_id" \
	and uid = user_id() \
) \
    drop type_name object_name \
go

/*  This macro drops a default referenced through the "sysobjects" table.  */
#define drop_default(default_name) drop_object(default_name, default, D)

/*  This macro drops a procedure referenced through the "sysobjects" table.  */
#define drop_proc(proc_name) drop_object(proc_name, procedure, P)

/*  This macro drops a rule referenced through the "sysobjects" table.  */
#define drop_rule(rule_name) drop_object(rule_name, rule, R)

/*  This macro drops a table referenced through the "sysobjects" table.  */
#define drop_table(table_name) drop_object(table_name, table, U)

/*  This macro drops a trigger referenced through the "sysobjects" table.  */
#define drop_trigger(trigger_name) drop_object(trigger_name, trigger, TR)

/*  This macro drops a view referenced through the "sysobjects" table.  */
#define drop_view(view_name) drop_object(view_name, view, V)



/*  This macro drops an index from a table.  */
#define drop_index(table_name, index_name) \
if exists ( \
    select * from sysindexes, sysobjects \
    where \
	sysindexes.id = sysobjects.id \
	and sysindexes.name = "index_name" \
	and sysindexes.indid > 0 and sysindexes.indid < 255 \
	and sysobjects.name = "table_name" \
	and sysobjects.type = "U" \
	and sysobjects.uid = user_id() \
) \
    drop index table_name.index_name \
go



/*  These are the key type ID's stored in the table "syskeys".  */
#define PRIMARY_KEY 1
#define COMMON_KEY  2
#define FOREIGN_KEY 3

/*  This macro drops a primary key from a table.  */
#define drop_primary_key(table_name) \
if exists ( \
    select * from syskeys, sysobjects \
    where \
	syskeys.id = sysobjects.id \
	and syskeys.type = PRIMARY_KEY \
	and sysobjects.name = "table_name" \
	and sysobjects.type = "U" \
	and sysobjects.uid = user_id() \
) \
    sp_dropkey primary, table_name \
go

/*  This macro drops a dependent key from a pair of tables.  */
#define drop_paired_key(key_type_id, key_type_name, table_name_1, 
table_name_2) \
if exists ( \
    select * from syskeys, sysobjects object_1, sysobjects object_2 \
    where \
	syskeys.id = object_1.id \
	syskeys.depid = object_2.id \
	and syskeys.type = key_type_id \
	and object_1.name = "table_name_1" \
	and object_1.type = "U" \
	and object_1.uid = user_id() \
	and object_2.name = "table_name_2" \
	and object_2.type = "U" \
	and object_2.uid = user_id() \
) \
    sp_dropkey key_type_name, table_name_1, table_name_2 \
go

/*  This macro drops a common key from a pair of tables.  */
#define drop_common_key(table_name_1, table_name_2) \
    drop_paired_key(COMMON_KEY, common, table_name_1, table_name_2)

/*  This macro drops a foreign key from a pair of tables.  */
#define drop_foreign_key(table_name_1, table_name_2) \
    drop_paired_key(FOREIGN_KEY, foreign, table_name_1, table_name_2)



/*  This macro drops a user-defined data type.  */
#define drop_type(type_name) \
if exists ( \
    select * from systypes \
    where \
	name = "type_name" \
	and uid = user_id() \
) \
    execute sp_droptype type_name \
go



#define grant_proc(proc_name) \
grant execute on proc_name to public \
go


/*
    Utility to print a variable.  The var "printval" must be defined as:
    declare @printvalmsg	varchar(255)
 */
#define printval(xxx) \
	select @printvalmsg="xxx " + "<" + convert(varchar(255),xxx) + ">" \
	print @printvalmsg

#endif





=================================================

An example: Don't comment on the quality of the stored proc !  :)


/*
	Add a point to the Geometry table
*/

drop_proc (sp_squirt_the_point)
go

create procedure sp_squirt_the_point (
	@fname   id,
	@lname   id,
	@geomtypeid  id=1,
	@line_index int,
	@x float,
	@y float,
	@length float
) as
begin
#ifdef DEBUG
	declare @printvalmsg    varchar(255)
	printval(@fname)
	printval(@lname)
	printval(@geomtypeid)
	printval(@line_index)
	printval(@x)
	printval(@y)
	printval(@length)
#endif /* DEBUG */
	declare @msg char(80)

	/* Put it in (so to speak :) */

	insert into Geometry values
	(
		@fname,
		@lname,
		@geomtypeid,
		@line_index,
		@x,
		@y,
		@length
	)

	if ( @@rowcount = 0 )
	begin
		select @msg = "Failed to insert values for " + rtrim(ltrim(convert (char, 
@fname))) + " and " + rtrim(ltrim(convert (char, @lname)))

		print @msg
		return SP_FAILURE
	end
end
go

grant_proc (sp_squirt_the_point)
go