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.