CREATE PROCEDURE dbo.aip_createhistbl
/*    Check for existance and number of history tables then
    create a new table if necessary required
    
    This procedure is called from aig_history program
    
    2005-08-04    George Curran, Computer Associates
            Add grant execute to role "aiadmin"
    2004-08-18    Yatin Dawada, Computer Associates
*/
(


--    History table serial number
    @strTblSuffix nvarchar(120),


--    History table limit
    @intTblLimit int = 100,  

--    Message severity
    @intMsgSev int = 0 OUTPUT,

--    History table default name less serial number
    @strTblName nvarchar(128) = 'ai_his_' OUTPUT


)
AS
--      Enable NOCOUNT option to suppress unnecessary messages
        SET NOCOUNT ON

--    Declare and initialize local variables
    DECLARE @strCmd nvarchar(2000)DECLARE @strSuffix nvarchar(120)
    DECLARE @strTbl nvarchar(128)

--    Verify table suffix is not empty or null
    IF    (@strTblSuffix = '')
    OR    (@strTblSuffix IS NULL)
    BEGIN
        SET @intMsgSev = 3
        RETURN (0)
    END



--    Set qualified table name
    SET    @strTbl = 'ai_his_' + @strTblSuffix

--    Create new table if table does not exist and creation will not exceed limit specified
--    for number of history tables
     IF    (NOT EXISTS (select * from dbo.sysobjects where id = object_id(@strTbl) and OBJECTPROPERTY(id, N'IsUserTable') = 1))
    BEGIN

--  If history table count exceeds the max settings
--  return 3 so ai can notify the user
     

        IF     ((SELECT COUNT(*) FROM sysobjects WHERE name LIKE 'ai_his_%') > @intTblLimit - 1) RETURN (3)

    
        SET    @strCmd = ''
        SET    @strCmd = @strCmd + CHAR(10) + 'CREATE    TABLE ' + @strTbl
        SET    @strCmd = @strCmd + CHAR(10) + '(    hisdate   datetime NOT NULL,'
        SET    @strCmd = @strCmd + CHAR(10) + '    QcTotal   int  NOT NULL'
        SET    @strCmd = @strCmd + CHAR(10) + 'CONSTRAINT    ' + @strTbl + '_PK'
        SET    @strCmd = @strCmd + CHAR(10) + 'PRIMARY KEY    (hisdate))'
             SET    @strCmd = @strCmd + CHAR(10) + '        ON      [PRIMARY]'
        SET    @strCmd = @strCmd + CHAR(10) + 'CREATE    INDEX   ' + @strTbl + '_AK1'
        SET    @strCmd = @strCmd + CHAR(10) + '    ON      ' + @strTbl + '(hisdate)'
        SET    @strCmd = @strCmd + CHAR(10) + '    ON      [PRIMARY]'
     
        EXEC (@strCmd)

        IF    @@ERROR = 0 RETURN (2)
                ELSE  RETURN (0)
  
   
    
    END

        RETURN (1)

--    Return
GO
GRANT EXECUTE ON  [dbo].[aip_createhistbl] TO [aiadmin]
GO