use @DBNAMEYEAR
if not exists (SELECT name 	  FROM 	 sysobjects 	  WHERE  name = N'AskMission' 	  AND 	 type = 'U')
begin 
begin tran createAskMission
CREATE TABLE [dbo].[AskMission](
	[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,
	[CITY] [numeric](6, 0) NULL,
	[ENDDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[ENDTIME] [char](5) COLLATE Arabic_CI_AS NULL,
	[ISSUEDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[LOCATION] [char](30) COLLATE Arabic_CI_AS NULL,
	[TYPE] [numeric](6, 0) NULL,
	[IsRejected] [bit] NULL,
	[RejectDate] [datetime] NULL,
	[ApproveDate] [datetime] NULL,
	[IsApproved] [bit] NULL,
	[IsArchived] [bit] NULL,
	[ArchiveDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	[IsDaily] [bit] NULL,
	[RejectCommand] [ntext] COLLATE Arabic_CI_AS NULL,
	[IsTransfer] [bit] NOT NULL DEFAULT ((0)),
	[status] [int] NULL,
	[lastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
	[userDescription] [text] COLLATE Arabic_CI_AS NULL,
	[DESCRIPT] [char](230) COLLATE Arabic_CI_AS NULL,
	[approvedBy] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
) ON [PRIMARY]  

SET ANSI_PADDING OFF
if @@ERROR > 0 
  rollback tran createAskMission
commit 
end
else
begin

begin tran AlterAskMission
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 = 'AskMission')
begin
select * into newAskMission  from  AskMission

drop table AskMission

CREATE TABLE [dbo].[AskMission](
	[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,
	[CITY] [numeric](6, 0) NULL,
	[ENDDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[ENDTIME] [char](5) COLLATE Arabic_CI_AS NULL,
	[ISSUEDATE] [char](5) COLLATE Arabic_CI_AS NULL,
	[LOCATION] [char](30) COLLATE Arabic_CI_AS NULL,
	[TYPE] [numeric](6, 0) NULL,
	[IsRejected] [bit] NULL,
	[RejectDate] [datetime] NULL,
	[ApproveDate] [datetime] NULL,
	[IsApproved] [bit] NULL,
	[IsArchived] [bit] NULL,
	[ArchiveDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	[IsDaily] [bit] NULL,
	[RejectCommand] [ntext] COLLATE Arabic_CI_AS NULL,
	[IsTransfer] [bit] NOT NULL DEFAULT ((0)),
	[status] [int] NULL,
	[lastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
	[userDescription] [text] COLLATE Arabic_CI_AS NULL,
	[DESCRIPT] [char](230) 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('newAskMission') and name='status' )
begin
EXECUTE ('
INSERT INTO AskMission
	           ( PERNO, BEGINDATE, BEGINTIME,CITY, ENDDATE, ENDTIME,ISSUEDATE,LOCATION, TYPE, isDaily, RejectCommand, IsTransfer, userDescription, 
								status,   lastChangeDate,IsApproved,approvedBy)
SELECT      PERNO, BEGINDATE, BEGINTIME,CITY, ENDDATE, ENDTIME,ISSUEDATE,LOCATION, TYPE, isDaily, RejectCommand, IsTransfer, DESCRIPT
					,  CASE  WHEN  IsRejected = 1 THEN 1   WHEN  IsApproved = 1 THEN 10   else 0	end   , UpdateDate,IsApproved,''000''
	 FROM         newAskMission  ')
end
else
begin

EXECUTE ('
INSERT INTO AskMission
	           (PERNO, BEGINDATE, BEGINTIME,CITY, ENDDATE, ENDTIME,ISSUEDATE,LOCATION, TYPE, isDaily, RejectCommand, IsTransfer, userDescription, 
								status,   lastChangeDate, IsApproved,approvedBy)
SELECT     PERNO, BEGINDATE, BEGINTIME,CITY, ENDDATE, ENDTIME,ISSUEDATE,LOCATION, TYPE, isDaily, RejectCommand, IsTransfer, userDescription, 
								status,   lastChangeDate , IsApproved,approvedBy
	 FROM         newAskMission  ')
 
end

drop table newAskMission
end

ALTER TABLE AskMission ALTER COLUMN [approvedBy] char(@pernoLenght)

if @@ERROR > 0 
  rollback tran AlterAskMission	
commit 

end
