Stored procedure

lunedì 15 gennaio 2007 - 12.19

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

iommif Profilo | Newbie

6 messaggi | Data Invio: lun 15 gen 2007 - 12:19

Ciao a tutti.
Spero di essere nel forum giusto.
Dovrei convertire una stored procedure scritta in sql server 2005 in un formato compatibile con sql server 2000.
Lo script è il seguente:

.....................................................................................................
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spRegConspumption]

@consumptionSampleCode varchar(50),
@consumptionTestCode varchar(50),
@objectID int,
@lotID int,
@consumptionConsumedUnits smallint,
@consumptionExecutionDate smalldatetime,
@consumptionCurve XML,
@consumptionReason smallint,
@cID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (cID int)

INSERT INTO Consumption OUTPUT INSERTED.consumptionID INTO @InsertTable
VALUES (@consumptionSampleCode, @consumptionTestCode, @objectID, @lotID, @consumptionConsumedUnits,
@consumptionExecutionDate, @consumptionCurve, @consumptionReason)

SET @cID = (SELECT cID FROM @InsertTable)
RETURN @cID

END
.....................................................................................................

Se tento di compilarlo con sql server 2000 mi dice che non riconosce la clausola OUTPUT.
Come lo correggo?
Grazie.

amelix Profilo | Expert

694 messaggi | Data Invio: lun 15 gen 2007 - 12:22

Prova così:

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Andrea
http://amelix.blogspot.com/index.html

iommif Profilo | Newbie

6 messaggi | Data Invio: lun 15 gen 2007 - 12:25

Prova questo script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[billType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[billType](
[billTypeID] [smallint] IDENTITY(1,1) NOT NULL,
[billTypeDescription] [varchar](50) NOT NULL,
CONSTRAINT [PK_billType] PRIMARY KEY CLUSTERED
(
[billTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reasons]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Reasons](
[reasonsID] [smallint] IDENTITY(1,1) NOT NULL,
[reasonsDescription] [varchar](50) NOT NULL,
CONSTRAINT [PK_Reasons] PRIMARY KEY CLUSTERED
(
[reasonsID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Repository]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Repository](
[repositoryID] [int] IDENTITY(1,1) NOT NULL,
[repositoryName] [varchar](20) NOT NULL,
[repositoryDescription] [varchar](50) NULL,
[repositoryState] [bit] NOT NULL,
CONSTRAINT [PK_Repository] PRIMARY KEY CLUSTERED
(
[repositoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRegConspumption]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRegConspumption]

@consumptionSampleCode varchar(50),
@consumptionTestCode varchar(50),
@objectID int,
@lotID int,
@consumptionConsumedUnits smallint,
@consumptionExecutionDate smalldatetime,
@consumptionCurve XML,
@consumptionReason smallint,
@cID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (cID int)

INSERT INTO Consumption OUTPUT INSERTED.consumptionID INTO @InsertTable
VALUES (@consumptionSampleCode, @consumptionTestCode, @objectID, @lotID, @consumptionConsumedUnits,
@consumptionExecutionDate, @consumptionCurve, @consumptionReason)

SET @cID = (SELECT cID FROM @InsertTable)
RETURN @cID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadStand]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadStand]

@repositoryID int,
@standName varchar(20),
@standDescription varchar(50),
@standType int,
@sID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (sID int)

INSERT INTO Stand OUTPUT INSERTED.standID INTO @InsertTable
VALUES (@repositoryID, @standName, @standType, @standDescription, 1)

SET @sID = (SELECT sID FROM @InsertTable)
RETURN @sID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ComponentType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ComponentType](
[componentTypeID] [smallint] IDENTITY(1,1) NOT NULL,
[componentTypeDescription] [varchar](80) NOT NULL,
CONSTRAINT [PK_ComponentType] PRIMARY KEY CLUSTERED
(
[componentTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lot]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lot](
[lotID] [int] IDENTITY(1,1) NOT NULL,
[lotCode] [varchar](50) NOT NULL,
[lotLoadDate] [smalldatetime] NOT NULL,
[lotExpirationDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Lot] PRIMARY KEY CLUSTERED
(
[lotID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StandType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[StandType](
[standTypeID] [smallint] IDENTITY(1,1) NOT NULL,
[standTypeDescription] [varchar](20) NOT NULL,
CONSTRAINT [PK_StandType] PRIMARY KEY CLUSTERED
(
[standTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Bill]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Bill](
[billID] [int] IDENTITY(1,1) NOT NULL,
[billName] [varchar](20) NOT NULL,
[billDescription] [varchar](50) NULL,
[billTypeID] [smallint] NOT NULL,
CONSTRAINT [PK_Bill] PRIMARY KEY CLUSTERED
(
[billID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BillMaterials]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[BillMaterials](
[billMaterialsID] [int] IDENTITY(1,1) NOT NULL,
[billID] [int] NOT NULL,
[componentID] [int] NULL,
[billMaterialsQuantity] [int] NULL,
[billMaterialsReason] [smallint] NULL,
[billMaterialsLotFlag] [bit] NULL,
[billMaterialsBill] [varchar](20) NULL,
CONSTRAINT [PK_BillMaterials] PRIMARY KEY CLUSTERED
(
[billMaterialsID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MaterialHandling]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[MaterialHandling](
[materialHandlingID] [int] IDENTITY(1,1) NOT NULL,
[materialHandlingOperatorCode] [varchar](50) NOT NULL,
[materialHandlingExecutedFunction] [varchar](50) NOT NULL,
[objectID] [int] NOT NULL,
[materialHandlingExcecutionDate] [smalldatetime] NOT NULL,
[materialHandlingReason] [smallint] NOT NULL,
CONSTRAINT [PK_MaterialHandling] PRIMARY KEY CLUSTERED
(
[materialHandlingID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reservation]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Reservation](
[reservationID] [int] IDENTITY(1,1) NOT NULL,
[reservationCode] [varchar](50) NOT NULL,
[objectID] [int] NOT NULL,
[reservationReserverdUnits] [smallint] NULL,
[reservationExpirationDate] [smalldatetime] NOT NULL,
[reservationReason] [smallint] NOT NULL,
[reservationActive] [bit] NOT NULL,
CONSTRAINT [PK_Reservation] PRIMARY KEY CLUSTERED
(
[reservationID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Consumption]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Consumption](
[consumptionID] [int] IDENTITY(1,1) NOT NULL,
[consumptionSampleCode] [varchar](50) NOT NULL,
[consumptionTestCode] [varchar](50) NOT NULL,
[objectID] [int] NOT NULL,
[lotID] [int] NOT NULL,
[consumptionConsumedUnits] [smallint] NOT NULL,
[consumptionExecutionDate] [smalldatetime] NOT NULL,
[consumptionCurve] [varchar](50) NULL,
[consumptionReason] [smallint] NOT NULL,
CONSTRAINT [PK_Consumption] PRIMARY KEY CLUSTERED
(
[consumptionID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Stand]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Stand](
[standID] [int] IDENTITY(1,1) NOT NULL,
[repositoryID] [int] NOT NULL,
[standName] [varchar](80) NOT NULL,
[standType] [smallint] NOT NULL,
[standDescription] [varchar](50) NULL,
[standState] [bit] NOT NULL,
CONSTRAINT [PK_Stand] PRIMARY KEY CLUSTERED
(
[standID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Object]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Object](
[objectID] [int] IDENTITY(1,1) NOT NULL,
[standID] [int] NOT NULL,
[componentID] [int] NOT NULL,
[lotID] [int] NOT NULL,
[objectAvaiableUnits] [smallint] NOT NULL,
[Position] [int] NULL,
CONSTRAINT [PK_Object] PRIMARY KEY CLUSTERED
(
[objectID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Component]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Component](
[componentID] [int] IDENTITY(1,1) NOT NULL,
[componentCode] [varchar](50) NOT NULL,
[componentDescription] [varchar](50) NOT NULL,
[componentType] [smallint] NOT NULL,
[componentUnitKind] [varchar](50) NOT NULL,
[componentFullQuantity] [smallint] NOT NULL,
[componentDiscardQuantity] [smallint] NOT NULL,
[componentMinimumStock] [smallint] NOT NULL,
CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED
(
[componentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadRepository]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadRepository]

@repositoryName varchar(20),
@repositoryDescription varchar(50),
@rID int OUTPUT

AS
BEGIN

DECLARE @InsertTable table (rID int)

INSERT INTO Repository OUTPUT INSERTED.repositoryID INTO @InsertTable
VALUES (@repositoryName, @repositoryDescription, 1)

SET @rID = (SELECT rID FROM @InsertTable)
RETURN @rID
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCloseRepository]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spCloseRepository]

@repositoryName varchar(20)

AS
BEGIN

IF (SELECT COUNT(*) FROM Stand
WHERE repositoryID = (SELECT repositoryID FROM Repository
WHERE repositoryName = @repositoryName) AND standState = 1) = 0
BEGIN
UPDATE Repository SET repositoryState = 0 WHERE repositoryName = @repositoryName
END

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveBill]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRemoveBill]

@BOMID int

AS
BEGIN

DELETE FROM BillMaterials WHERE billID = @BOMID

DELETE FROM Bill WHERE billID = @BOMID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadBillMaterials]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadBillMaterials]

@billID int,
@componentID int,
@billMaterialsQuantity int,
@billMaterialsReason smallint,
@billMaterialsLotFlag bit,
@billMaterialsBill varchar(20),
@bMID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (bmID int)

INSERT INTO BillMaterials OUTPUT INSERTED.billMaterialsID INTO @InsertTable
VALUES (@billID, @componentID, @billMaterialsQuantity, @billMaterialsReason, @billMaterialsLotFlag, @billMaterialsBill)

SET @bMID = (SELECT bMID FROM @InsertTable)
RETURN @bMID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCloseStand]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spCloseStand]

@standName varchar(20)

AS
BEGIN

DECLARE @standID int

SET @standID = (SELECT standID FROM Stand WHERE standName = @standName)

IF (SELECT COUNT(*) FROM object
WHERE standID = @standID AND objectAvaiableUnits = 0) = 0
BEGIN
UPDATE Stand SET standState = 0 WHERE standName = @standName
END

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUnloadObject]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spUnloadObject]

@opCode varchar(20),
@objectID int,
@removedUnits smallint,
@reasonID int

AS
BEGIN

UPDATE Object SET objectAvaiableUnits = objectAvaiableUnits - @removedUnits WHERE objectID = @objectID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object UnLoaded'', @objectID , GETDATE(), @reasonID)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveObjectByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRemoveObjectByID]

@opCode varchar(20),
@objectID int,
@reasonID int

AS
BEGIN

UPDATE Object SET objectAvaiableUnits = 0 WHERE objectID = @objectID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object UnLoaded'', @objectID , GETDATE(), @reasonID)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveObjectByLot]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRemoveObjectByLot]

@opCode varchar(20),
@componentID int,
@lotID int,
@reasonID int

AS
BEGIN

DECLARE @objID int

SET @objID = (SELECT objectID FROM Object WHERE componentID = @componentID AND lotID = @lotID)

UPDATE Object SET objectAvaiableUnits = 0 WHERE lotID = @lotID AND componentID = @componentID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object UnLoaded'', @objID , GETDATE(), @reasonID)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveComponent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[spRemoveComponent]

@componentCode varchar(50),
@cID int OUTPUT

AS
BEGIN

SET @cID = (SELECT componentID FROM Component WHERE componentCode = @componentCode)
IF (SELECT COUNT(*) FROM object WHERE componentID = @cID AND objectAvaiableUnits > 0) = 0
BEGIN
DELETE FROM Component WHERE componentID = @cID
RETURN @cID
END
ELSE
BEGIN
SET @cID = 0
RETURN @cID
END
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadObject]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadObject]

@opCode varchar(20),
@standID int,
@componentID int,
@lotID int,
@objectUnits smallint,
@position int,
@reasonID smallint,
@objID int OUTPUT

AS
BEGIN

DECLARE @idTableVar table(oID int)

IF (SELECT lotExpirationDate FROM Lot WHERE lotID = @lotID) > GETDATE()
BEGIN
SET @objID = (SELECT O.objectID FROM (Object O INNER JOIN Component C ON O.componentID = C.componentID)
INNER JOIN Lot L ON O.lotID = L.lotID
WHERE O.standID = @standID AND O.componentID = @componentID AND O.lotID = @lotID)

IF @objID IS NULL
BEGIN
INSERT INTO Object OUTPUT INSERTED.objectID INTO @idTableVar
VALUES (@standID, @componentID, @lotID, @objectUnits, @position)

SET @objID = (SELECT oID FROM @idTableVar)
INSERT INTO MaterialHandling VALUES (@opCode, ''Object Loaded'', @objID , GETDATE(), @reasonID)

RETURN @objID
END
ELSE
BEGIN
UPDATE Object SET objectAvaiableUnits = objectAvaiableUnits + @objectUnits
WHERE objectID = @objID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object Loaded'', @objID , GETDATE(), @reasonID)

RETURN @objID
END
END
ELSE
BEGIN
SET @objID = 0
RETURN @objID
END
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spSetReservation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spSetReservation]

@reservationCode varchar(50),
@objectID int,
@reservationReservedUnits smallint,
@reservationExpirationDate smalldatetime,
@reservationReason smallint,
@rID int OUTPUT
AS
BEGIN

DECLARE @idInsertTable table(rID int)

INSERT INTO Reservation OUTPUT INSERTED.reservationID INTO @idInsertTable
VALUES (@reservationCode, @objectID, @reservationReservedUnits, @reservationExpirationDate,
@reservationReason, 1)

SET @rID = (SELECT rID FROM @idInsertTable)
RETURN @rID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spDeleteReservation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spDeleteReservation]

@reservationCode varchar(50)

AS
BEGIN

UPDATE Reservation SET reservationActive = 0 WHERE reservationCode = @reservationCode

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadComponent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadComponent]

@componentCode varchar(50),
@componentDescription varchar(50),
@componentType smallint,
@componentUnitKind varchar(50),
@componentFullQuantity smallint,
@componentDiscardQuantity smallint,
@componentMinimumStock smallint,
@cID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (cID int)

INSERT INTO Component OUTPUT INSERTED.componentID INTO @InsertTable
VALUES (@componentCode, @componentDescription, @componentType, @componentUnitKind,
@componentFullQuantity, @componentDiscardQuantity, @componentMinimumStock)

SET @cID = (SELECT cID FROM @InsertTable)
RETURN @cID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetComponentByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spGetComponentByID]

@cID int,
@code varchar(50) OUTPUT,
@Description varchar(50) OUTPUT,
@Type int OUTPUT,
@unitKind varchar(50) OUTPUT,
@fullQuantity int OUTPUT,
@discardQuantity int OUTPUT,
@minimumStock int OUTPUT

AS
BEGIN

SET @code = (SELECT componentCode FROM Component WHERE componentID = @cID)
SET @Description = (SELECT componentDescription FROM Component WHERE componentID = @cID)
SET @Type = (SELECT componentType FROM Component WHERE componentID = @cID)
SET @unitKind = (SELECT componentUnitKind FROM Component WHERE componentID = @cID)
SET @fullQuantity = (SELECT componentFullQuantity FROM Component WHERE componentID = @cID)
SET @discardQuantity = (SELECT componentDiscardQuantity FROM Component WHERE componentID = @cID)
SET @minimumStock = (SELECT componentMinimumStock FROM Component WHERE componentID = @cID)

--ETURN @code
--RETURN @Description
--RETURN @Type
--RETURN @unitKind
--RETURN @fullQuantity
--RETURN @discardQuantity
--RETURN @minimumStock

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetComponentByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spGetComponentByName]

@cCode varchar(50),
@cID int OUTPUT,
@Description varchar(50) OUTPUT,
@Type int OUTPUT,
@unitKind varchar(50) OUTPUT,
@fullQuantity int OUTPUT,
@discardQuantity int OUTPUT,
@minimumStock int OUTPUT

AS
BEGIN

SET @cID = (SELECT componentID FROM Component WHERE componentCode = @cCode)
SET @Description = (SELECT componentDescription FROM Component WHERE componentCode = @cCode)
SET @Type = (SELECT componentType FROM Component WHERE componentCode = @cCode)
SET @unitKind = (SELECT componentUnitKind FROM Component WHERE componentCode = @cCode)
SET @fullQuantity = (SELECT componentFullQuantity FROM Component WHERE componentCode = @cCode)
SET @discardQuantity = (SELECT componentDiscardQuantity FROM Component WHERE componentCode = @cCode)
SET @minimumStock = (SELECT componentMinimumStock FROM Component WHERE componentCode = @cCode)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadBill]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadBill]

@billName varchar(20),
@billDescription varchar(50),
@billTypeID smallint,
@bID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (bID int)

INSERT INTO Bill OUTPUT INSERTED.billID INTO @InsertTable
VALUES (@billName, @billDescription, @billTypeID)

SET @bID = (SELECT bID FROM @InsertTable)
RETURN @bID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadLot]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadLot]

@lotCode varchar(50),
@lotExpirationDate Datetime,
@lotID int OUTPUT

AS
BEGIN

IF @lotExpirationDate > GETDATE()
BEGIN
DECLARE @InsertTable table (lID int)

INSERT INTO Lot OUTPUT INSERTED.lotID INTO @InsertTable
VALUES (@lotCode, GETDATE(), @lotExpirationDate)

SET @lotID = (SELECT lID FROM @InsertTable)
END
ELSE
BEGIN
SET @lotID = 0
END

RETURN @lotID

END

'
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Bill_billType]') AND type = 'F')
ALTER TABLE [dbo].[Bill] WITH CHECK ADD CONSTRAINT [FK_Bill_billType] FOREIGN KEY([billTypeID])
REFERENCES [dbo].[billType] ([billTypeID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BillMaterials_Bill]') AND type = 'F')
ALTER TABLE [dbo].[BillMaterials] WITH CHECK ADD CONSTRAINT [FK_BillMaterials_Bill] FOREIGN KEY([billID])
REFERENCES [dbo].[Bill] ([billID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BillMaterials_Component]') AND type = 'F')
ALTER TABLE [dbo].[BillMaterials] WITH CHECK ADD CONSTRAINT [FK_BillMaterials_Component] FOREIGN KEY([componentID])
REFERENCES [dbo].[Component] ([componentID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BillMaterials_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[BillMaterials] WITH CHECK ADD CONSTRAINT [FK_BillMaterials_Reasons] FOREIGN KEY([billMaterialsReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_MaterialHandling_Object]') AND type = 'F')
ALTER TABLE [dbo].[MaterialHandling] WITH CHECK ADD CONSTRAINT [FK_MaterialHandling_Object] FOREIGN KEY([objectID])
REFERENCES [dbo].[Object] ([objectID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_MaterialHandling_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[MaterialHandling] WITH CHECK ADD CONSTRAINT [FK_MaterialHandling_Reasons] FOREIGN KEY([materialHandlingReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_MaterialHandling_Reasons1]') AND type = 'F')
ALTER TABLE [dbo].[MaterialHandling] WITH CHECK ADD CONSTRAINT [FK_MaterialHandling_Reasons1] FOREIGN KEY([materialHandlingReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Reservation_Object]') AND type = 'F')
ALTER TABLE [dbo].[Reservation] WITH CHECK ADD CONSTRAINT [FK_Reservation_Object] FOREIGN KEY([objectID])
REFERENCES [dbo].[Object] ([objectID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Reservation_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[Reservation] WITH CHECK ADD CONSTRAINT [FK_Reservation_Reasons] FOREIGN KEY([reservationReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Consumption_Lot]') AND type = 'F')
ALTER TABLE [dbo].[Consumption] WITH CHECK ADD CONSTRAINT [FK_Consumption_Lot] FOREIGN KEY([lotID])
REFERENCES [dbo].[Lot] ([lotID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Consumption_Object]') AND type = 'F')
ALTER TABLE [dbo].[Consumption] WITH CHECK ADD CONSTRAINT [FK_Consumption_Object] FOREIGN KEY([objectID])
REFERENCES [dbo].[Object] ([objectID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Consumption_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[Consumption] WITH CHECK ADD CONSTRAINT [FK_Consumption_Reasons] FOREIGN KEY([consumptionReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Stand_Repository]') AND type = 'F')
ALTER TABLE [dbo].[Stand] WITH CHECK ADD CONSTRAINT [FK_Stand_Repository] FOREIGN KEY([repositoryID])
REFERENCES [dbo].[Repository] ([repositoryID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Stand_StandType]') AND type = 'F')
ALTER TABLE [dbo].[Stand] WITH CHECK ADD CONSTRAINT [FK_Stand_StandType] FOREIGN KEY([standType])
REFERENCES [dbo].[StandType] ([standTypeID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Object_Component]') AND type = 'F')
ALTER TABLE [dbo].[Object] WITH CHECK ADD CONSTRAINT [FK_Object_Component] FOREIGN KEY([componentID])
REFERENCES [dbo].[Component] ([componentID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Object_Lot]') AND type = 'F')
ALTER TABLE [dbo].[Object] WITH CHECK ADD CONSTRAINT [FK_Object_Lot] FOREIGN KEY([lotID])
REFERENCES [dbo].[Lot] ([lotID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Object_Stand]') AND type = 'F')
ALTER TABLE [dbo].[Object] WITH CHECK ADD CONSTRAINT [FK_Object_Stand] FOREIGN KEY([standID])
REFERENCES [dbo].[Stand] ([standID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Component_ComponentType]') AND type = 'F')
ALTER TABLE [dbo].[Component] WITH CHECK ADD CONSTRAINT [FK_Component_ComponentType] FOREIGN KEY([componentType])
REFERENCES [dbo].[ComponentType] ([componentTypeID])

amelix Profilo | Expert

694 messaggi | Data Invio: lun 15 gen 2007 - 12:30

Ho aggiornato il msg di prima...
Avevi ragione tu!
Avevo sbagliato macchina e l'ho provata su una macchina 2005....

PS. Ho messo CREATE al posto di ALTER
PPS. Non ho provato quello che mi hai dato come secondo... Troppo lungo & complicato x capire che cosa fasesse veramente...
Andrea
http://amelix.blogspot.com/index.html

iommif Profilo | Newbie

6 messaggi | Data Invio: lun 15 gen 2007 - 12:34

L'errore che ritorna è questo:
Server: Msg 170, Level 15, State 1, Procedure spRegConspumption, Line 21
Line 21: Incorrect syntax near 'OUTPUT'.

amelix Profilo | Expert

694 messaggi | Data Invio: lun 15 gen 2007 - 12:48

Anche quella che ti ho dato io?
Andrea
http://amelix.blogspot.com/index.html

iommif Profilo | Newbie

6 messaggi | Data Invio: lun 15 gen 2007 - 12:55

Scusa se ti faccio perdere tempo, non l'ho provata perchè sono un po a digiuno dell'argomento.
Puoi controllarmi questo script:

CREATE PROCEDURE [dbo].[spLoadLot]

@lotCode varchar(50),
@lotExpirationDate Datetime,
@lotID int OUTPUT

AS
BEGIN

IF @lotExpirationDate > GETDATE()
BEGIN
DECLARE @InsertTable table (lID int)

INSERT INTO Lot OUTPUT INSERTED.lotID INTO @InsertTable
VALUES (@lotCode, GETDATE(), @lotExpirationDate)

SET @lotID = (SELECT lID FROM @InsertTable)
END
ELSE
BEGIN
SET @lotID = 0
END

RETURN @lotID

END

e dirmi dove è l'errore?
Grazie.

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: lun 15 gen 2007 - 13:26

>INSERT INTO Lot OUTPUT INSERTED.lotID INTO @InsertTable
>VALUES (@lotCode, GETDATE(), @lotExpirationDate)

L'errore è qua e stai tranquillo che non funziona nemmeno in SQL Server 2005

Il comando è errato ed inoltre viene referenziata la tabella virtuale INSERTED che è disponibile solo in un trigger di INSERT/UPDATE.

Forse è meglio che ci spieghi dettagliatamente lo scopo che vuoi raggiungere, postando la struttura delle tabelle interessate, alcuni dati di prova ed il risultato finale.

>Grazie.
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

iommif Profilo | Newbie

6 messaggi | Data Invio: lun 15 gen 2007 - 13:54

tu hai perfettamente ragione.
Questo script di creazione di un db mi è stato consegnato in quanto la persona che prima se ne occupava è sparita senza lasciare traccia mettendo l'azienda per cui lavoro nei guai.
Lo script si occupa di creare delle tabelle e stored procedure all'interno di un db.
Tale script è stato creato in SQL SERVER 2005.
Dato che il cliente ha anche dei pc vecchi vorrebbe utilizzare questo script anche con SQL SERVER 2000.

Ti posto tutto lo script, spero tu possa aiutarmi.

Grazie ancora per la pazienza.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[billType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[billType](
[billTypeID] [smallint] IDENTITY(1,1) NOT NULL,
[billTypeDescription] [varchar](50) NOT NULL,
CONSTRAINT [PK_billType] PRIMARY KEY CLUSTERED
(
[billTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reasons]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Reasons](
[reasonsID] [smallint] IDENTITY(1,1) NOT NULL,
[reasonsDescription] [varchar](50) NOT NULL,
CONSTRAINT [PK_Reasons] PRIMARY KEY CLUSTERED
(
[reasonsID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Repository]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Repository](
[repositoryID] [int] IDENTITY(1,1) NOT NULL,
[repositoryName] [varchar](20) NOT NULL,
[repositoryDescription] [varchar](50) NULL,
[repositoryState] [bit] NOT NULL,
CONSTRAINT [PK_Repository] PRIMARY KEY CLUSTERED
(
[repositoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRegConspumption]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRegConspumption]

@consumptionSampleCode varchar(50),
@consumptionTestCode varchar(50),
@objectID int,
@lotID int,
@consumptionConsumedUnits smallint,
@consumptionExecutionDate smalldatetime,
@consumptionCurve XML,
@consumptionReason smallint,
@cID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (cID int)

INSERT INTO Consumption OUTPUT INSERTED.consumptionID INTO @InsertTable
VALUES (@consumptionSampleCode, @consumptionTestCode, @objectID, @lotID, @consumptionConsumedUnits,
@consumptionExecutionDate, @consumptionCurve, @consumptionReason)

SET @cID = (SELECT cID FROM @InsertTable)
RETURN @cID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadStand]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadStand]

@repositoryID int,
@standName varchar(20),
@standDescription varchar(50),
@standType int,
@sID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (sID int)

INSERT INTO Stand OUTPUT INSERTED.standID INTO @InsertTable
VALUES (@repositoryID, @standName, @standType, @standDescription, 1)

SET @sID = (SELECT sID FROM @InsertTable)
RETURN @sID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ComponentType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ComponentType](
[componentTypeID] [smallint] IDENTITY(1,1) NOT NULL,
[componentTypeDescription] [varchar](80) NOT NULL,
CONSTRAINT [PK_ComponentType] PRIMARY KEY CLUSTERED
(
[componentTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lot]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lot](
[lotID] [int] IDENTITY(1,1) NOT NULL,
[lotCode] [varchar](50) NOT NULL,
[lotLoadDate] [smalldatetime] NOT NULL,
[lotExpirationDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Lot] PRIMARY KEY CLUSTERED
(
[lotID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StandType]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[StandType](
[standTypeID] [smallint] IDENTITY(1,1) NOT NULL,
[standTypeDescription] [varchar](20) NOT NULL,
CONSTRAINT [PK_StandType] PRIMARY KEY CLUSTERED
(
[standTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Bill]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Bill](
[billID] [int] IDENTITY(1,1) NOT NULL,
[billName] [varchar](20) NOT NULL,
[billDescription] [varchar](50) NULL,
[billTypeID] [smallint] NOT NULL,
CONSTRAINT [PK_Bill] PRIMARY KEY CLUSTERED
(
[billID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BillMaterials]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[BillMaterials](
[billMaterialsID] [int] IDENTITY(1,1) NOT NULL,
[billID] [int] NOT NULL,
[componentID] [int] NULL,
[billMaterialsQuantity] [int] NULL,
[billMaterialsReason] [smallint] NULL,
[billMaterialsLotFlag] [bit] NULL,
[billMaterialsBill] [varchar](20) NULL,
CONSTRAINT [PK_BillMaterials] PRIMARY KEY CLUSTERED
(
[billMaterialsID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MaterialHandling]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[MaterialHandling](
[materialHandlingID] [int] IDENTITY(1,1) NOT NULL,
[materialHandlingOperatorCode] [varchar](50) NOT NULL,
[materialHandlingExecutedFunction] [varchar](50) NOT NULL,
[objectID] [int] NOT NULL,
[materialHandlingExcecutionDate] [smalldatetime] NOT NULL,
[materialHandlingReason] [smallint] NOT NULL,
CONSTRAINT [PK_MaterialHandling] PRIMARY KEY CLUSTERED
(
[materialHandlingID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Reservation]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Reservation](
[reservationID] [int] IDENTITY(1,1) NOT NULL,
[reservationCode] [varchar](50) NOT NULL,
[objectID] [int] NOT NULL,
[reservationReserverdUnits] [smallint] NULL,
[reservationExpirationDate] [smalldatetime] NOT NULL,
[reservationReason] [smallint] NOT NULL,
[reservationActive] [bit] NOT NULL,
CONSTRAINT [PK_Reservation] PRIMARY KEY CLUSTERED
(
[reservationID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Consumption]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Consumption](
[consumptionID] [int] IDENTITY(1,1) NOT NULL,
[consumptionSampleCode] [varchar](50) NOT NULL,
[consumptionTestCode] [varchar](50) NOT NULL,
[objectID] [int] NOT NULL,
[lotID] [int] NOT NULL,
[consumptionConsumedUnits] [smallint] NOT NULL,
[consumptionExecutionDate] [smalldatetime] NOT NULL,
[consumptionCurve] [varchar](50) NULL,
[consumptionReason] [smallint] NOT NULL,
CONSTRAINT [PK_Consumption] PRIMARY KEY CLUSTERED
(
[consumptionID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Stand]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Stand](
[standID] [int] IDENTITY(1,1) NOT NULL,
[repositoryID] [int] NOT NULL,
[standName] [varchar](80) NOT NULL,
[standType] [smallint] NOT NULL,
[standDescription] [varchar](50) NULL,
[standState] [bit] NOT NULL,
CONSTRAINT [PK_Stand] PRIMARY KEY CLUSTERED
(
[standID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Object]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Object](
[objectID] [int] IDENTITY(1,1) NOT NULL,
[standID] [int] NOT NULL,
[componentID] [int] NOT NULL,
[lotID] [int] NOT NULL,
[objectAvaiableUnits] [smallint] NOT NULL,
[Position] [int] NULL,
CONSTRAINT [PK_Object] PRIMARY KEY CLUSTERED
(
[objectID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Component]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Component](
[componentID] [int] IDENTITY(1,1) NOT NULL,
[componentCode] [varchar](50) NOT NULL,
[componentDescription] [varchar](50) NOT NULL,
[componentType] [smallint] NOT NULL,
[componentUnitKind] [varchar](50) NOT NULL,
[componentFullQuantity] [smallint] NOT NULL,
[componentDiscardQuantity] [smallint] NOT NULL,
[componentMinimumStock] [smallint] NOT NULL,
CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED
(
[componentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadRepository]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadRepository]

@repositoryName varchar(20),
@repositoryDescription varchar(50),
@rID int OUTPUT

AS
BEGIN

DECLARE @InsertTable table (rID int)

INSERT INTO Repository OUTPUT INSERTED.repositoryID INTO @InsertTable
VALUES (@repositoryName, @repositoryDescription, 1)

SET @rID = (SELECT rID FROM @InsertTable)
RETURN @rID
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCloseRepository]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spCloseRepository]

@repositoryName varchar(20)

AS
BEGIN

IF (SELECT COUNT(*) FROM Stand
WHERE repositoryID = (SELECT repositoryID FROM Repository
WHERE repositoryName = @repositoryName) AND standState = 1) = 0
BEGIN
UPDATE Repository SET repositoryState = 0 WHERE repositoryName = @repositoryName
END

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveBill]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRemoveBill]

@BOMID int

AS
BEGIN

DELETE FROM BillMaterials WHERE billID = @BOMID

DELETE FROM Bill WHERE billID = @BOMID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadBillMaterials]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadBillMaterials]

@billID int,
@componentID int,
@billMaterialsQuantity int,
@billMaterialsReason smallint,
@billMaterialsLotFlag bit,
@billMaterialsBill varchar(20),
@bMID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (bmID int)

INSERT INTO BillMaterials OUTPUT INSERTED.billMaterialsID INTO @InsertTable
VALUES (@billID, @componentID, @billMaterialsQuantity, @billMaterialsReason, @billMaterialsLotFlag, @billMaterialsBill)

SET @bMID = (SELECT bMID FROM @InsertTable)
RETURN @bMID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCloseStand]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spCloseStand]

@standName varchar(20)

AS
BEGIN

DECLARE @standID int

SET @standID = (SELECT standID FROM Stand WHERE standName = @standName)

IF (SELECT COUNT(*) FROM object
WHERE standID = @standID AND objectAvaiableUnits = 0) = 0
BEGIN
UPDATE Stand SET standState = 0 WHERE standName = @standName
END

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUnloadObject]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spUnloadObject]

@opCode varchar(20),
@objectID int,
@removedUnits smallint,
@reasonID int

AS
BEGIN

UPDATE Object SET objectAvaiableUnits = objectAvaiableUnits - @removedUnits WHERE objectID = @objectID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object UnLoaded'', @objectID , GETDATE(), @reasonID)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveObjectByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRemoveObjectByID]

@opCode varchar(20),
@objectID int,
@reasonID int

AS
BEGIN

UPDATE Object SET objectAvaiableUnits = 0 WHERE objectID = @objectID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object UnLoaded'', @objectID , GETDATE(), @reasonID)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveObjectByLot]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spRemoveObjectByLot]

@opCode varchar(20),
@componentID int,
@lotID int,
@reasonID int

AS
BEGIN

DECLARE @objID int

SET @objID = (SELECT objectID FROM Object WHERE componentID = @componentID AND lotID = @lotID)

UPDATE Object SET objectAvaiableUnits = 0 WHERE lotID = @lotID AND componentID = @componentID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object UnLoaded'', @objID , GETDATE(), @reasonID)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spRemoveComponent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[spRemoveComponent]

@componentCode varchar(50),
@cID int OUTPUT

AS
BEGIN

SET @cID = (SELECT componentID FROM Component WHERE componentCode = @componentCode)
IF (SELECT COUNT(*) FROM object WHERE componentID = @cID AND objectAvaiableUnits > 0) = 0
BEGIN
DELETE FROM Component WHERE componentID = @cID
RETURN @cID
END
ELSE
BEGIN
SET @cID = 0
RETURN @cID
END
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadObject]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadObject]

@opCode varchar(20),
@standID int,
@componentID int,
@lotID int,
@objectUnits smallint,
@position int,
@reasonID smallint,
@objID int OUTPUT

AS
BEGIN

DECLARE @idTableVar table(oID int)

IF (SELECT lotExpirationDate FROM Lot WHERE lotID = @lotID) > GETDATE()
BEGIN
SET @objID = (SELECT O.objectID FROM (Object O INNER JOIN Component C ON O.componentID = C.componentID)
INNER JOIN Lot L ON O.lotID = L.lotID
WHERE O.standID = @standID AND O.componentID = @componentID AND O.lotID = @lotID)

IF @objID IS NULL
BEGIN
INSERT INTO Object OUTPUT INSERTED.objectID INTO @idTableVar
VALUES (@standID, @componentID, @lotID, @objectUnits, @position)

SET @objID = (SELECT oID FROM @idTableVar)
INSERT INTO MaterialHandling VALUES (@opCode, ''Object Loaded'', @objID , GETDATE(), @reasonID)

RETURN @objID
END
ELSE
BEGIN
UPDATE Object SET objectAvaiableUnits = objectAvaiableUnits + @objectUnits
WHERE objectID = @objID

INSERT INTO MaterialHandling VALUES (@opCode, ''Object Loaded'', @objID , GETDATE(), @reasonID)

RETURN @objID
END
END
ELSE
BEGIN
SET @objID = 0
RETURN @objID
END
END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spSetReservation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spSetReservation]

@reservationCode varchar(50),
@objectID int,
@reservationReservedUnits smallint,
@reservationExpirationDate smalldatetime,
@reservationReason smallint,
@rID int OUTPUT
AS
BEGIN

DECLARE @idInsertTable table(rID int)

INSERT INTO Reservation OUTPUT INSERTED.reservationID INTO @idInsertTable
VALUES (@reservationCode, @objectID, @reservationReservedUnits, @reservationExpirationDate,
@reservationReason, 1)

SET @rID = (SELECT rID FROM @idInsertTable)
RETURN @rID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spDeleteReservation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spDeleteReservation]

@reservationCode varchar(50)

AS
BEGIN

UPDATE Reservation SET reservationActive = 0 WHERE reservationCode = @reservationCode

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadComponent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadComponent]

@componentCode varchar(50),
@componentDescription varchar(50),
@componentType smallint,
@componentUnitKind varchar(50),
@componentFullQuantity smallint,
@componentDiscardQuantity smallint,
@componentMinimumStock smallint,
@cID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (cID int)

INSERT INTO Component OUTPUT INSERTED.componentID INTO @InsertTable
VALUES (@componentCode, @componentDescription, @componentType, @componentUnitKind,
@componentFullQuantity, @componentDiscardQuantity, @componentMinimumStock)

SET @cID = (SELECT cID FROM @InsertTable)
RETURN @cID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetComponentByID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spGetComponentByID]

@cID int,
@code varchar(50) OUTPUT,
@Description varchar(50) OUTPUT,
@Type int OUTPUT,
@unitKind varchar(50) OUTPUT,
@fullQuantity int OUTPUT,
@discardQuantity int OUTPUT,
@minimumStock int OUTPUT

AS
BEGIN

SET @code = (SELECT componentCode FROM Component WHERE componentID = @cID)
SET @Description = (SELECT componentDescription FROM Component WHERE componentID = @cID)
SET @Type = (SELECT componentType FROM Component WHERE componentID = @cID)
SET @unitKind = (SELECT componentUnitKind FROM Component WHERE componentID = @cID)
SET @fullQuantity = (SELECT componentFullQuantity FROM Component WHERE componentID = @cID)
SET @discardQuantity = (SELECT componentDiscardQuantity FROM Component WHERE componentID = @cID)
SET @minimumStock = (SELECT componentMinimumStock FROM Component WHERE componentID = @cID)

--ETURN @code
--RETURN @Description
--RETURN @Type
--RETURN @unitKind
--RETURN @fullQuantity
--RETURN @discardQuantity
--RETURN @minimumStock

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetComponentByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spGetComponentByName]

@cCode varchar(50),
@cID int OUTPUT,
@Description varchar(50) OUTPUT,
@Type int OUTPUT,
@unitKind varchar(50) OUTPUT,
@fullQuantity int OUTPUT,
@discardQuantity int OUTPUT,
@minimumStock int OUTPUT

AS
BEGIN

SET @cID = (SELECT componentID FROM Component WHERE componentCode = @cCode)
SET @Description = (SELECT componentDescription FROM Component WHERE componentCode = @cCode)
SET @Type = (SELECT componentType FROM Component WHERE componentCode = @cCode)
SET @unitKind = (SELECT componentUnitKind FROM Component WHERE componentCode = @cCode)
SET @fullQuantity = (SELECT componentFullQuantity FROM Component WHERE componentCode = @cCode)
SET @discardQuantity = (SELECT componentDiscardQuantity FROM Component WHERE componentCode = @cCode)
SET @minimumStock = (SELECT componentMinimumStock FROM Component WHERE componentCode = @cCode)

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadBill]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadBill]

@billName varchar(20),
@billDescription varchar(50),
@billTypeID smallint,
@bID int OUTPUT

AS
BEGIN
DECLARE @InsertTable table (bID int)

INSERT INTO Bill OUTPUT INSERTED.billID INTO @InsertTable
VALUES (@billName, @billDescription, @billTypeID)

SET @bID = (SELECT bID FROM @InsertTable)
RETURN @bID

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoadLot]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[spLoadLot]

@lotCode varchar(50),
@lotExpirationDate Datetime,
@lotID int OUTPUT

AS
BEGIN

IF @lotExpirationDate > GETDATE()
BEGIN
DECLARE @InsertTable table (lID int)

INSERT INTO Lot OUTPUT INSERTED.lotID INTO @InsertTable
VALUES (@lotCode, GETDATE(), @lotExpirationDate)

SET @lotID = (SELECT lID FROM @InsertTable)
END
ELSE
BEGIN
SET @lotID = 0
END

RETURN @lotID

END

'
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Bill_billType]') AND type = 'F')
ALTER TABLE [dbo].[Bill] WITH CHECK ADD CONSTRAINT [FK_Bill_billType] FOREIGN KEY([billTypeID])
REFERENCES [dbo].[billType] ([billTypeID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BillMaterials_Bill]') AND type = 'F')
ALTER TABLE [dbo].[BillMaterials] WITH CHECK ADD CONSTRAINT [FK_BillMaterials_Bill] FOREIGN KEY([billID])
REFERENCES [dbo].[Bill] ([billID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BillMaterials_Component]') AND type = 'F')
ALTER TABLE [dbo].[BillMaterials] WITH CHECK ADD CONSTRAINT [FK_BillMaterials_Component] FOREIGN KEY([componentID])
REFERENCES [dbo].[Component] ([componentID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_BillMaterials_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[BillMaterials] WITH CHECK ADD CONSTRAINT [FK_BillMaterials_Reasons] FOREIGN KEY([billMaterialsReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_MaterialHandling_Object]') AND type = 'F')
ALTER TABLE [dbo].[MaterialHandling] WITH CHECK ADD CONSTRAINT [FK_MaterialHandling_Object] FOREIGN KEY([objectID])
REFERENCES [dbo].[Object] ([objectID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_MaterialHandling_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[MaterialHandling] WITH CHECK ADD CONSTRAINT [FK_MaterialHandling_Reasons] FOREIGN KEY([materialHandlingReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_MaterialHandling_Reasons1]') AND type = 'F')
ALTER TABLE [dbo].[MaterialHandling] WITH CHECK ADD CONSTRAINT [FK_MaterialHandling_Reasons1] FOREIGN KEY([materialHandlingReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Reservation_Object]') AND type = 'F')
ALTER TABLE [dbo].[Reservation] WITH CHECK ADD CONSTRAINT [FK_Reservation_Object] FOREIGN KEY([objectID])
REFERENCES [dbo].[Object] ([objectID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Reservation_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[Reservation] WITH CHECK ADD CONSTRAINT [FK_Reservation_Reasons] FOREIGN KEY([reservationReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Consumption_Lot]') AND type = 'F')
ALTER TABLE [dbo].[Consumption] WITH CHECK ADD CONSTRAINT [FK_Consumption_Lot] FOREIGN KEY([lotID])
REFERENCES [dbo].[Lot] ([lotID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Consumption_Object]') AND type = 'F')
ALTER TABLE [dbo].[Consumption] WITH CHECK ADD CONSTRAINT [FK_Consumption_Object] FOREIGN KEY([objectID])
REFERENCES [dbo].[Object] ([objectID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Consumption_Reasons]') AND type = 'F')
ALTER TABLE [dbo].[Consumption] WITH CHECK ADD CONSTRAINT [FK_Consumption_Reasons] FOREIGN KEY([consumptionReason])
REFERENCES [dbo].[Reasons] ([reasonsID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Stand_Repository]') AND type = 'F')
ALTER TABLE [dbo].[Stand] WITH CHECK ADD CONSTRAINT [FK_Stand_Repository] FOREIGN KEY([repositoryID])
REFERENCES [dbo].[Repository] ([repositoryID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Stand_StandType]') AND type = 'F')
ALTER TABLE [dbo].[Stand] WITH CHECK ADD CONSTRAINT [FK_Stand_StandType] FOREIGN KEY([standType])
REFERENCES [dbo].[StandType] ([standTypeID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Object_Component]') AND type = 'F')
ALTER TABLE [dbo].[Object] WITH CHECK ADD CONSTRAINT [FK_Object_Component] FOREIGN KEY([componentID])
REFERENCES [dbo].[Component] ([componentID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Object_Lot]') AND type = 'F')
ALTER TABLE [dbo].[Object] WITH CHECK ADD CONSTRAINT [FK_Object_Lot] FOREIGN KEY([lotID])
REFERENCES [dbo].[Lot] ([lotID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Object_Stand]') AND type = 'F')
ALTER TABLE [dbo].[Object] WITH CHECK ADD CONSTRAINT [FK_Object_Stand] FOREIGN KEY([standID])
REFERENCES [dbo].[Stand] ([standID])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Component_ComponentType]') AND type = 'F')
ALTER TABLE [dbo].[Component] WITH CHECK ADD CONSTRAINT [FK_Component_ComponentType] FOREIGN KEY([componentType])
REFERENCES [dbo].[ComponentType] ([componentTypeID])

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dimenticavo di dirti che lo script originale è stato realizzato per SQL SERVER 2005 e funziona perfettamente, quando ho tentato di generare lo script di creazione per la versione 2000 alcune parti sono state commentate.

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: lun 15 gen 2007 - 14:41

>Questo script di creazione di un db mi è stato consegnato in
>quanto la persona che prima se ne occupava è sparita senza lasciare
>traccia mettendo l'azienda per cui lavoro nei guai.

Il comando:

INSERT INTO Consumption OUTPUT INSERTED.consumptionID INTO @InsertTable....

funziona solo in SQL Server 2005, non è supportato dalle edizioni precedenti.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

iommif Profilo | Newbie

6 messaggi | Data Invio: lun 15 gen 2007 - 16:01

Quale sarebbe l'equivalente in SQL SERVER 2000?

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: lun 15 gen 2007 - 16:03

>Quale sarebbe l'equivalente in SQL SERVER 2000?
Non c'è questa funzionalità; Se vuoi replicare quella logica devi eseguire una INSERT seguita da una SELECT.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !