use @DBNAME
begin tran createWebClockDmp
if not exists (SELECT name 	  FROM 	 sysobjects 	  WHERE  name = N'webClockDmp' 	  AND 	 type = 'U')
begin 

 CREATE TABLE [dbo].[webClockDmp](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[KARTNO] [char](8) COLLATE Arabic_CI_AS NOT NULL,
	[CLOCKDATE] [char](10) COLLATE Arabic_CI_AS NOT NULL,
	[CLOCKTIME] [char](5) COLLATE Arabic_CI_AS NOT NULL,
	[PERNO] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
	[IO_TYPE] [char](1) COLLATE Arabic_CI_AS NULL,
	[status] [int] NULL,
	[requestDate] [char](10) COLLATE Arabic_CI_AS NULL,
	[requestTime] [char](5) COLLATE Arabic_CI_AS NULL,
	[lastChangeDate] [char](10) COLLATE Arabic_CI_AS NULL,
	[isTransfer] [bit] NULL,
	[userDescription] [ntext] COLLATE Arabic_CI_AS NULL,
	[adminDescription] [ntext] COLLATE Arabic_CI_AS NULL,
	[approvedBy] [char](@pernoLenght) COLLATE Arabic_CI_AS NULL,
	[EvacuationSystem] [int] NULL,
	[SystemName] nvarchar(50) NULL
) ON [PRIMARY]

end
else
begin
IF EXISTS (SELECT * FROM syscolumns WHERE  name = 'approvedBy' AND id = object_Id(N'webClockDmp')) 
ALTER TABLE webClockDmp Alter Column approvedBy char(@pernoLenght) NULL

IF EXISTS (SELECT * FROM syscolumns WHERE  name = 'CLOCKDATE' AND id = object_Id(N'webClockDmp')) 
ALTER TABLE webClockDmp Alter Column [CLOCKDATE] [char](10) COLLATE Arabic_CI_AS NOT NULL

end

If Not Exists(SELECT     id
                FROM         syscolumns
                WHERE     (name = 'ComCode') AND (id = OBJECT_ID(N'webClockDmp'))) 
    ALTER TABLE dbo.webClockDmp ADD ComCode numeric(6, 0) NULL

If Not Exists(SELECT     id
                FROM         syscolumns
                WHERE     (name = 'ClockCode') AND (id = OBJECT_ID(N'webClockDmp'))) 
    ALTER TABLE dbo.webClockDmp ADD ClockCode char(3) NULL

If Not Exists(SELECT     id
                FROM         syscolumns
                WHERE     (name = 'SystemName') AND (id = OBJECT_ID(N'webClockDmp'))) 
    ALTER TABLE dbo.webClockDmp ADD SystemName nvarchar(50) NULL

If Not Exists(SELECT     id
                FROM         syscolumns
                WHERE     (name = 'EvacuationSystem') AND (id = OBJECT_ID(N'webClockDmp'))) 
    ALTER TABLE dbo.webClockDmp ADD EvacuationSystem int NULL

update webClockDmp set CLOCKDATE = '13'+ CLOCKDATE where len(CLOCKDATE) < 10

if @@ERROR > 0 
  rollback tran createWebClockDmp	
commit 