把在Sql Server 下的数据脚本迁移到 Sybase ASA

本人由于最近的项目需求，需把以前在Sql Server中实现的数据脚本改到ASA下，需要注意以下几点（以下左边为SqlServer，右边为ASA）

Nchar????????????----->char * 2

Nvarcher??????----->varcher * 2

结尾可以加“；”----->结尾不可以加“；”，ASA中“；”相当于“GO”

exec(@sql)???----->exec @sql??执行存贮过程，

?????????????????????----->execute(@sql)执行sql语句

?????????????????????----->execute @sql执行存贮过程

set?? l?? select?? @variant ="" ----->select @variant=""

触发器中的After??????? ----->for

?下面举几个常用的例子：

1、创建触发器

---------------SQL Server
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TrgName]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
?DROP TRIGGER [dbo].[TrgName]
CREATE TRIGGER TrgName ON?TableName
?AFTER DELETE
AS

--------触发器内容
GO

---------------ASA Trigger
行级别
INSERT 触发器
CREATE TRIGGER check_birth_date?AFTER INSERT ON Employee?--插入后触发
CREATE TRIGGER mytrigger BEFORE INSERT ON Employee??--插入前触发
REFERENCING NEW AS new_employee
FOR EACH ROW
BEGIN
?? DECLARE err_user_error EXCEPTION
?? FOR SQLSTATE '99999';
?? IF new_employee.birth_date > 'June 6, 2001' THEN
????? SIGNAL err_user_error;
?? END IF;
END
DELETE 触发器示例
CREATE TRIGGER mytrigger BEFORE DELETE ON employee
CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING OLD AS oldtable
FOR EACH ROW
BEGIN
?? ...
END
语句级别 UPDATE 触发器示例
CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING NEW AS table_after_update
??????????? OLD AS table_before_update
FOR EACH STATEMENT
BEGIN
?? ...
END

2、查询指定表列名和类型

SQL Server 2000:
select? sc.name,st.name from syscolumns as sc INNER join systypes as st on st.xtype=sc.xtype
where sc.id in (select id FROM sysobjects? WHERE? name = 'TableName' AND type = 'U')order by id

ASA:
select column_name,domain_name from SYS.SYSCOLUMN join SYS.SYSDOMAIN where table_id in(select table_id from SYS.SYSTABLE where table_name='TableName' )order by column_id

3、创建存贮过程

-----------SQL Server
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ProName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
?DROP PROCEDURE [dbo].[ProName]
CREATE PROCEDURE ProName
? @Name1?NVARCHAR(256),
? @Name2?NVARCHAR(256) = NULL
AS
-------------内容
GO

-----------ASA
if exists(
?? select 1 from sys.sysprocedure
?? where proc_name='ProName'
???? and creator=user_id('DBA')
)? drop procedure DBA.ProName

GO
CREATE PROCEDURE AddSubscribeInfo
@Name1?VARCHAR(512),
@Name2?VARCHAR(512) = NULL
AS
-------------内容
GO