Using a stored procedure to generate a unique document ID

Tips and Tricks - Applies to Infiniti v8.0 or later.

There are many techniques by which an automatic document ID can be generated, one ‘quick win’ method is using a stored procedure and table to generate a row for each record.

The table and associated stored procedure scripts provided below is a basic example only and should be modified to suit the situation.

Sample Table

/***** Object: Table [dbo].[Records] Script Date: 03/29/2011 13:46:43 *****/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Records](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Author] [nvarchar](50) NULL,

[DateCreated] [date] NULL

) ON [PRIMARY]

GO

Sample Stored procedure

/***** Object: StoredProcedure [dbo].[sp_CreateRecord] Script Date: 03/29/2011 13:47:23 *****/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_CreateRecord]

--Input params

@Title varchar(100)

AS

BEGIN

--Next Line is necessary - Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

SET NOCOUNT ON

--insert the new record

INSERT INTO Records (Author, dateCreated )

VALUES (@Title, getdate())

--return the entire record created by the stored proc

select [id]

,Author

,dateCreated

from Records where ID = @@IDENTITY

END

GO

Adding the stored procedure to Director

Add the stored procedure to the appropriate data source in Manage. Usually the stored procedure would be configured as an invisible data source question in Design

Related Articles

 
Keywords
 
transact sql mssql mysql database oracle datasource dataobject adddate