SQL stored procedures that return an incremented number

CREATE TABLE [AutoResponses]
([id] [int] IDENTITY (1, 1) NOT NULL,
[Responding PF] [varchar] (50) NOT NULL,
[Sender email] [varchar] (100) NOT NULL,
[Sender name] [varchar] (100) NOT NULL,
[Subject] [varchar] (100) NULL,
[Entry date] [smalldatetime] NOT NULL)
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_AutoResponses] ON [AutoResponses]([Responding PF], [Sender name]) ON [PRIMARY]
GO
ALTER TABLE [AutoResponses] WITH NOCHECK
ADD CONSTRAINT [DF_AutoResponses_Entry date] DEFAULT (getdate()) FOR [Entry date],
CONSTRAINT [PK_AutoResponses] PRIMARY KEY NONCLUSTERED ([id])
ON [PRIMARY]
GO

INSERT INTO [AutoResponses]
([Responding PF]
, [Sender email]
, [Sender name]
, Subject)
VALUES
("a","b","c","d")

Notice the id field is incremented automatically and the [Entry Date] field is automatically set by the default getdate()

Select @@identity

CREATE Procedure NewResponse
 @pf varchar(50)
 ,@email varchar(100)
 ,@name varchar(100)
 ,@subj varchar(100)
 ,@autonum int OUTPUT
AS
 begin tran
 insert AutoResponses
 ([Responding PF], [Sender email], [Sender name], Subject)
 values
 (@pf,@email, @name, @subj)
 set @autonum = @@identity
 commit tran
GO

Using VB code to insert the new data and retrieve the new id value can now be done in one move, using the Data Environment (DE) or the connection object:

Private Function GetTrackingNum() As Long
 Dim resp As Long
 DE.NewResponse m_RequestingPF, m_Senderemail, m_SenderName, m_Subject, resp
 GetTrackingNum = resp
End Function

By encapsulating this process into a stored procedure and a class function call, you can create a VB class for a DLL that accepts 4 parameters corresponding to the input parameters of the SQL Stored procedures and returns one value -- the new id.

Public Property Get TrackingNumber() As Long
   TrackingNumber = GetTrackingNum()
End Property

Public Property Let Senderemail(ByVal vData As String)
  m_Senderemail = vData
End Property

Public Property Let SenderName(ByVal vData As String)
  m_SenderName = vData
End Property

Public Property Let RequestingPF(ByVal vData As String)
  m_RequestingPF = vData
End Property

Public Property Let Subject(ByVal vData As String)
  m_Subject = Trim(vData)
End Property

You can register this DLL on Exchange Server and then have a script call it every time a new message arrives in a Public Folder

CREATE PROCEDURE sp_GetRandomRecord
AS

declare @nRecordCount int
declare @nRandNum int

-- Create a temporary table with the same structure of
-- the table we want to select a random record from
CREATE TABLE #TempTable
(
  field1 varchar(255),
  field2 varchar(50),
  field3 int,
  idNum int identity(1,1)
)

-- Dump the contents of the table to seach into the 
-- temp. table
INSERT INTO #TempTable
Select field1, field2, field3 From myTable

-- Get the number of records in our temp table
Select @nRecordCount = count(*) From #TempTable

-- Select a random number between 1 and the number
-- of records in our table
Select @nRandNum = Round(((@nRecordCount - 2) * Rand() + 1), 0)

-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select field1, field2, field3 From #TempTable
Where idNum = @nRandNum
Go