Sybase过程中动态SQL使用:
--DROP PROCEDURE dbo.AUTO_ABM_TO_AAS
CREATE PROCEDURE dbo.AUTO_ABM_TO_AAS(@L_MSG VARCHAR(255) OUTPUT)
AS
BEGIN
---复制分析表结构及内容
DECLARE @TABLE1 VARCHAR(64) --原表名
DECLARE @TABLE2 VARCHAR(64) --现表名
DECLARE @sqlstring VARCHAR(500)
DECLARE @ADV_ERROR INT
DECLARE TABLE_NAME1 CURSOR FOR
SELECT name FROM sysobjects WHERE name like 'ABM%' AND type='U'
OPEN TABLE_NAME1
FETCH TABLE_NAME1 INTO @TABLE1
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TABLE2=STR_REPLACE(@TABLE1,'ABM_','AAS_')
----动态SQL处理
SELECT @sqlstring= 'SELECT * INTO '+ @TABLE2+ ' FROM '+ @TABLE1
EXECUTE (@sqlstring) --执行语句
-- EXEC dbo.TABLE_COPY @TABLE1,@TABLE2
FETCH TABLE_NAME1 INTO @TABLE1
END
CLOSE TABLE_NAME1
SELECT @ADV_ERROR = @@ERROR
IF @ADV_ERROR <> 0
BEGIN
SELECT @L_MSG = '创建出现错误!'
END
ELSE
BEGIN
SELECT @L_MSG = '创建成功!'
END
COMMIT
END