use @DBNAMEYEAR
if not exists (SELECT name 	  FROM 	 sysobjects 	  WHERE  name = N'AskAbsence' 	  AND 	 type = 'U')
begin 
begin tran createAskAbsence
CREATE TABLE [dbo].[AskAbsence](
	[PAGENO] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
	[PERNO] [char](@pernoLenght) COLLATE Arabic_CI_AS NOT NULL,
	[BEGINDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[BEGINTIME] [char](5) COLLATE Arabic_CI_AS NULL,
	[ENDDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[ENDTIME] [char](5) COLLATE Arabic_CI_AS NULL,
	[TYPE] [numeric](6, 0) NULL,
	[IsRejected] [bit] NULL,
	[RejectDate] [datetime] NULL,
	[IsArchived] [bit] NULL,
	[ArchiveDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	[ApproveDate] [datetime] NULL,
	[RECDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[isDaily] [bit] NULL,
	[IsApproved] [bit] NULL,
	[RejectCommand] [ntext] COLLATE Arabic_CI_AS NULL,
	[IsTransfer] [bit] NOT NULL DEFAULT ((0)),
	[userDescription] [text] COLLATE Arabic_CI_AS NULL,
	[status] [int] NULL CONSTRAINT [DF_AskAbsence_status]  DEFAULT ((0)),
	[lastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
	[Descript] [char](230) COLLATE Arabic_CI_AS NULL,
	[approvedBy] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
) ON [PRIMARY]  

if @@ERROR > 0 
  rollback tran createAskAbsence	
commit 
end
else
begin
begin tran AlterAskAbsence
if not exists ( SELECT t.TABLE_NAME   FROM  INFORMATION_SCHEMA.COLUMNS AS c JOIN  INFORMATION_SCHEMA.TABLES AS t    ON t.TABLE_NAME = c.TABLE_NAME WHERE    COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1 AND t.TABLE_NAME = 'AskAbsence')
begin
select * into newAskAbsence  from  AskAbsence

drop table AskAbsence

CREATE TABLE [dbo].[AskAbsence](
	[PAGENO] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
	[PERNO] [char](@pernoLenght) COLLATE Arabic_CI_AS NOT NULL,
	[BEGINDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[BEGINTIME] [char](5) COLLATE Arabic_CI_AS NULL,
	[DESCRIPT] [char](60) COLLATE Arabic_CI_AS NULL,
	[ENDDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[ENDTIME] [char](5) COLLATE Arabic_CI_AS NULL,
	[TYPE] [numeric](6, 0) NULL,
	[IsRejected] [bit] NULL,
	[RejectDate] [datetime] NULL,
	[IsArchived] [bit] NULL,
	[ArchiveDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	[ApproveDate] [datetime] NULL,
	[RECDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[isDaily] [bit] NULL,
	[IsApproved] [bit] NULL,
	[RejectCommand] [ntext] COLLATE Arabic_CI_AS NULL,
	[IsTransfer] [bit] NOT NULL DEFAULT (0),
	[userDescription] [text] COLLATE Arabic_CI_AS NULL,
	[status] [int] NULL CONSTRAINT [DF_AskAbsence_status]  DEFAULT ((0)),
	[lastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
	[approvedBy] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
) ON [PRIMARY]  

if  not exists (SELECT name FROM  syscolumns WHERE  id = OBJECT_ID('newAskAbsence') and name='status' )
begin
EXECUTE ('
INSERT INTO AskAbsence
	           ( PERNO, BEGINDATE, BEGINTIME, ENDDATE, ENDTIME, TYPE, RECDATE, isDaily,IsApproved, RejectCommand, IsTransfer, userDescription, 
								status,   lastChangeDate,approvedBy)
SELECT      PERNO, BEGINDATE, BEGINTIME, ENDDATE, ENDTIME, TYPE, RECDATE, isDaily,IsApproved, RejectCommand, IsTransfer, DESCRIPT
					,  CASE  WHEN  IsRejected = 1 THEN 1   WHEN  IsApproved = 1 THEN 10   else 0	end   , UpdateDate , ''000''
	 FROM         newAskAbsence  
')
end
else
begin
EXECUTE('
INSERT INTO AskAbsence
	           ( PERNO, BEGINDATE, BEGINTIME, ENDDATE, ENDTIME, TYPE, RECDATE, isDaily,IsApproved, RejectCommand, IsTransfer, userDescription, 
								status,   lastChangeDate,approvedBy)
SELECT      PERNO, BEGINDATE, BEGINTIME, ENDDATE, ENDTIME, TYPE, RECDATE, isDaily,IsApproved, RejectCommand, IsTransfer, DESCRIPT
					,  status  , lastChangeDate,approvedBy
	 FROM         newAskAbsence  ')
end

drop table newAskAbsence
end
ALTER TABLE askAbsence ALTER COLUMN [approvedBy] char(@pernoLenght)

if @@ERROR > 0 
  rollback tran AlterAskAbsence	
commit 

end
