2uC8zwqc

1CREATE PROCEDURE [dbo].[BackUpTable] 
2        @TableName sysname
3    AS
4    BEGIN
5
6        SET nocount ON 
7
8        DECLARE @sql VARCHAR(500) 
9
10        IF EXISTS (SELECT * 
11               FROM   sys.objects 
12               WHERE  object_id = Object_id(N'[dbo].[' + @TableName+'_EST' + ']') 
13                      AND TYPE IN ( N'U' )) 
14
15            BEGIN 
16              SET @sql = 'declare @Done bit
17                          set @Done = 0
18                          while @Done = 0
19                          begin
20                            delete top (100000)
21                            from ' + @TableName + '_Bak' + 
22                            ' if @@rowcount = 0     
23                                set @Done = 1  
24                          end;' 
25              SET @sql = @sql + 'insert into ' + @TableName + '_Bak select * from ' + 
26                         @TableName +'_EST'
27
28              EXEC(@sql) 
29            END 
30        ELSE 
31            BEGIN 
32              DECLARE @err_message VARCHAR(300) 
33
34              SELECT @err_message = 'The table "' + Isnull(@TableName, 'null') + 
35                                '" does not exist' 
36
37              RAISERROR (@err_message, 16, 1) 
38
39            END  
40
41    END
42	
43CREATE OR REPLACE PROCEDURE BackUpTable (IN TableName VARCHAR(128))
44DYNAMIC RESULT SETS 1
45	
46DECLARE dynamicSql  VARCHAR(500); 
47
48IF(EXISTS( 
49    SELECT * FROM SYSIBM.SYSTABLES
50         WHERE NAME =  TableName||'_EST'
51        ) 
52)
53
54THEN 
55
56       SET dynamicSql  = 'DELETE FROM '||TableName ||'_BAK';
57
58       SET dynamicSql  = dynamicSql  ||'insert into ' || TableName || '_BAK select * from ' || 
59                 TableName || '_EST';
60
61      EXECUTE IMMEDIATE dynamicSql; 
62
63ELSE
64
65DECLARE v_sqlstate CHAR(5);    
66DECLARE v_sqlcode INT;
67
68DECLARE SQLSTATE CHAR(5) DEFAULT '00000';   
69DECLARE SQLCODE INT DEFAULT 0;
70
71DECLARE EXIT HANDLER FOR SQLEXCEPTION  
72
73BEGIN
74
75SELECT SQLSTATE, SQLCODE
76INTO v_sqlstate, v_sqlcode
77FROM sysibm.sysdummy1;    
78
79SET O_Error_Msg = 'TABLE IS NOT AVAILABLE:: SQLState : '||v_sqlstate||' SQLCode : '||v_sqlcode ;   
80
81END;
82
83END IF;
84	
85IF( EXISTS( SELECT 1 FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'YOURLIB' AND TABLE_NAME = 'YOURTABLENAME')) THEN
86DROP TABLE YOURLIB.YOURTABLENAME;
87END IF;