quote:

---

Originally posted by merlintintin

I'm not very satisfied by this solution.. I have simplified my script for you but it was more complicated...

The script could/must be executed 2 times without error..





DECLARE @zoneUId CHAR(9)


IF NOT EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].ACS_ZONE') )
BEGIN
CREATE TABLE dbo.ACS_ZONE
(
UId T_UID NOT NULL,
Version T_VERSION NOT NULL,
Name T_NAME NOT NULL,
BuiltIn T_BOOL NOT NULL,
Comment T_COMMENT NULL,
)

ALTER TABLE dbo.ACS_ZONE ADD CONSTRAINT PK_ACS_ZONE PRIMARY KEY (UId)

-- INSERT UID INTO SYS_UID
INSERT INTO SYS_UID (Name,KeyCurrentValue,HighValueSize,LowValueSize,SysType)
VALUES ('ACS_ZONE','00000',5,4,'E')

-- INSERT INTO ACS_ZONE
INSERT INTO ACS_ZONE ([UId],[Version],[Name],[BuiltIn],[Comment])
VALUES (dbo.GetNextUId('ACS_ZONE','1'), '000000000','System',1,'Default zone');
SET @zoneUId = ( SELECT UId FROM ACS_ZONE WHERE Name = 'System' )
END



-- ADD COLUMN: ACS_ACL.ZoneUId
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ACS_ACL'
AND COLUMN_NAME = 'ZoneUId' )
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID
GO

SET @zoneUId = ( SELECT UId FROM ACS_ZONE WHERE Name = 'System' )
UPDATE dbo.ACS_ACL SET ZoneUId = @zoneUId WHERE ZoneUId IS NULL
GO

ALTER TABLE dbo.ACS_ACL ALTER COLUMN ZoneUId T_UID NOT NULL
ALTER TABLE dbo.ACS_ACL ADD CONSTRAINT FK_ACS_ACL_ACS_ZONE FOREIGN KEY (ZoneUId) REFERENCES ACS_ZONE (UId)
ALTER TABLE dbo.ACS_ACL DROP CONSTRAINT PK_ACS_ACL
ALTER TABLE dbo.ACS_ACL ADD CONSTRAINT PK_ACS_ACL PRIMARY KEY (GroupUId,ResourceUId,PermissionUId, ZoneUId)



MESSAGE:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@zoneUId".

If I place a GO, the scalar variable is not defined anymore...

How to proceed by writing all statement in BEGIN AND clause ?

---