"UpdateDocument" Stored Procedure

This stored procedure is used by the Documents edit page to add a new document item to the database, or apply changes to an existing document item. The input parameters include the item's primary key (ItemID), plus category, and friendly name.

The document item stores either an URL to browse the document, or the actual contents of the of the document. If supplied, the document content is stored as an image field to support binary as well as text formats. The HTTP content type for the document and the content size in bytes are also stored. The maximum document size that may be stored is 2,147,483,647 bytes.

        
   CREATE PROCEDURE Portal_UpdateDocument
   (
       @ItemID           int,
       @ModuleID         int,
       @FileFriendlyName nvarchar(150),
       @FileNameUrl      nvarchar(250),
       @UserName         nvarchar(100),
       @Category         nvarchar(50),
       @Content          image,
       @ContentType      nvarchar(50),
       @ContentSize      int
   )
   AS
   IF (@ItemID=0) OR NOT EXISTS (
       SELECT 
           * 
       FROM Portal_Documents 
       WHERE 
           ItemID = @ItemID
   )
   INSERT INTO Portal_Documents
   (
       ModuleID,
       FileFriendlyName,
       FileNameUrl,
       CreatedByUser,
       CreatedDate,
       Category,
       Content,
       ContentType,
       ContentSize
   )
   
   VALUES
   (
       @ModuleID,
       @FileFriendlyName,
       @FileNameUrl,
       @UserName,
       GetDate(),
       @Category,
       @Content,
       @ContentType,
       @ContentSize
   )
   ELSE
   
   BEGIN
   
   IF (@ContentSize=0)
   
   UPDATE Portal_Documents
   
   SET 
       CreatedByUser    = @UserName,
       CreatedDate      = GetDate(),
       Category         = @Category,
       FileFriendlyName = @FileFriendlyName,
       FileNameUrl      = @FileNameUrl
   
   WHERE
       ItemID = @ItemID
   ELSE
   
   UPDATE Portal_Documents
   
   SET
       CreatedByUser     = @UserName,
       CreatedDate       = GetDate(),
       Category          = @Category,
       FileFriendlyName  = @FileFriendlyName,
       FileNameUrl       = @FileNameUrl,
       Content           = @Content,
       ContentType       = @ContentType,
       ContentSize       = @ContentSize
   
   WHERE
       ItemID = @ItemID
   
   END
        

Documents:  Each record in the Documents table is a single item, as displayed by the Documents Portal Module. Since all Documents modules store their record in this table, each item contains a ModuleID to permit related items to be retrieved in a single query.

The primary key in this table is the ItemID identity field.