[Sybase] Stored procedure tips

 

Some commands regarding tables/procedures

 

Sp_help Procedure_name/ Table_name – Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as computed columns and function-based indexes. This is similar to desc (describe) command in oracle.

 

Sp_helptext  Procedure_name - Displays the source text of a compiled object. Displays the source text of computed columns or function-based index definitions.

 

Sp_depends  - Displays information about database object dependencies—the view(s), trigger(s), and procedure(s)—in the database that depend on a specified table or view, and the table(s) and view(s) in the database on which the specified view, trigger, or procedure depends. This is useful in the procedures when you are deleting/updating something you can check where all it can effect.

 

 

How to use variables

 

Always define the variable with appropriate length. Make sure you are not defining the variable with any arbitrary length. It should be equal to the length of columns or may be little more. Don’t unnecessary increase the length of variable.

 

Example:

 

Declare @sql_insert varchar(9999) – it will waste the space and may decrease the performance.

 

 

Convert function – this function is used to convert the column values from one data type to other date type

 

Example:

 

select column_A, convert(char(12), column_B)

from Table_name

 

Print -  This command is very useful in debugging and if you want something to print on output window. Please note it prints only char and varchar values. No other datatypes are possible.

 

Example:

 

Print ‘Procedure created successfully’

 

 

To find table name using column name –  This is useful in case you know the column name but not aware of table name then you can use the following query.

select sysobjects.name from sysobjects,syscolumns

where

sysobjects.id = syscolumns.id and

syscolumns.name like 'clnt_id_n'

 

In case your result set is more than 1. Please verify with other columns.

 

Date Formats –

Different projects requires different date formats. Sybase provides 11 types of date formats. So we can use them based on our need.

 

Different Sybase DateTime Formats

Format #	Format	Description	Format #	Format
0	mon dd ccyy hh:mm xM	Default	100	mon dd ccyy hh:mmxM
1	mm/dd/yy	USA	101	mm/dd/ccyy
2	yy.mm.dd	ANSI	102	ccyy.mm.dd
3	dd/mm/yy	English/French	103	dd/mm/ccyy
4	dd.mm.yy	German	104	dd.mm.ccyy
5	dd-mm-yy		105	dd-mm-ccyy
6	dd mon yy		106	dd mon ccyy
7	mon dd, yy		107	mon dd, ccyy
8	hh:mm:ss		108	hh:mm:ss
9	mon d ccyy hh:mm:ss:tttxM	default + milliseconds	109	mon d ccyy hh:mm:ss:tttxM
10	mm-dd-yy	USA	110	mm-dd-ccyy
11	yy/mm/dd	Japan	111	ccyy/mm/dd

Examples -

Format #	String	Format #	String
0	Jan 3 2002 9:12AM	100	Jan 3 2002 9:12AM
1	01/03/02	101	01/03/2002
2	02.01.03	102	2002.01.03
3	03/01/02	103	03/01/2002
4	03.01.02	104	03.01.2002
5	03-01-02	105	03-01-2002
6	03 Jan 02	106	03 Jan 2002
7	Jan 03, 02	107	Jan 03, 2002
8	09:12:34	108	09:12:34
9	Jan 3 2002 9:12:34:566AM	109	Jan 3 2002 9:12:34:566AM
10	01-03-02	110	01-03-2002
11	02/01/03	111	2002/01/03
12	020103	112	20020103

 

How to use different formats–

               

Select @Variable_name = "CONVERT(char,column_name,101)"

 

Also there are certain more things which you should take care

1.       Take care of aliases while you define the joins(inner join, outer join). Alias should be properly defined and make sure you are not addressing to any wrong column.

2.       In case of union, the sequence should ke that you are using the taking the union of same column and same datatype.

For example:

 

Correct format –

 

Select column_a (int), column_b(varchar)

…………………………………..

Union

Select column_a (int), column_b(varchar)

………………………………………

 

Incorrect format –

 

Select column_a (int), column_b(varchar)

…………………………………..

Union

Select column_b(varchar), column_a (int)

………………………………………

               This will report an error.

 

Sharing knowledge from my colleague - Nadeep.