1.2. fejezet, MS-SQL tárolt eljárás

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
 
CREATE  PROCEDURE CreateValuesRecord
  @DocID INT,
  @DocTypeID INT
AS
  DECLARE @Script nvarchar(4000)
  DECLARE @TblName NCHAR(64)
 
SELECT
  @TblName=[DocType]
FROM [DocType]
WHERE [DocTypeID] = @DocTypeID
 
SELECT @Script = 'INSERT INTO ['+RTRIM(@TblName)+'](DocID) '+
  'VALUES( '+Str(@DocID)+')'
EXEC(@Script)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE SelectTopDoc
  @TopLowValue INT,
  @DocRegBookID INT
 AS
SELECT
  RegNum,
  DateOfDisp,
  [DateTime]=DocActDiary.[DateTime],
  DocID=DocActDiary.DocID
INTO #Registered
FROM DocRegister JOIN DocActDiary
  ON DocRegister.DocActDiaryID=DocActDiary.DocActDiaryID
WHERE (DocRegister.DocRegBookID=@DocRegBookID) AND
  (RegNumber >= @TopLowValue) AND (SubNum IS NULL)
 
SELECT
  DocIncoming.CustomerID,
  DocIncoming.SubjID,
  DocIncoming.DocKindID,
  DocActDiary.DocID
INTO #Incoming
FROM DocIncoming JOIN DocActDiary
 ON DocIncoming.DocActDiaryID=DocActDiary.DocActDiaryID
 
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
  #Registered.RegNum,
  #Registered.DateOfDisp,
  #Registered.[DateTime],
  #Incoming.CustomerID,
  #Incoming.SubjID,
  #Incoming.DocKindID,
  #Registered.DocID,
  [Name] = LastName+' '+MiddleName+' '+FirstName,
  Subject.Subject
FROM #Registered JOIN #Incoming
  ON #Registered.DocID=#Incoming.DocID
  JOIN Customer ON #Incoming.CustomerID = Customer.CustomerID
  JOIN Subject ON #Incoming.SubID = Subject.SubID
 
ORDER BY RegisteringNumber
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO