iVend Database Database : GetTransactionData Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTransactionKey

In

Reference Key of the Transaction

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[GetTransactionData]

(

@pTransactionKey nvarchar(50)

)

AS

BEGIN

DECLARE @lFromQuery   NVARCHAR(200),

  @lTableName   NVARCHAR(50),

  @lExcludeColumns NVARCHAR(max)

-- Creating temprory table to store insert statements.

CREATE TABLE #TempTransactionData

(

 InsertQuery nvarchar(max)

)

-- Getting list of all transaction tables.

SELECT * INTO #TransactionTable FROM information_schema.tables

WHERE TABLE_NAME Like 'Trx%' AND TABLE_NAME NOT IN ('TrxScannedDataHeader','TrxScannedDataDetail','TrxTransactionSignature','TrxTransactionReceipt')

ORDER BY TABLE_NAME

WHILE EXISTS (SELECT * FROM #TransactionTable)

BEGIN

SELECT TOP 1 @lTableName = TABLE_NAME FROM #TransactionTable

-- Check for TransactionKey column.

IF EXISTS(SELECT * FROM sys.columns WHERE [name] = N'TransactionKey' AND [object_id] = OBJECT_ID(@lTableName))

BEGIN

  -- Creating where condition to generate insert statements.

  SET @lFromQuery = 'From ' + @lTableName + ' WHERE TransactionKey = ''' + @pTransactionKey + ''''

  SELECT @lExcludeColumns = COALESCE(@lExcludeColumns + ',', '') + C.name FROM sys.columns C, Sys.tables T WHERE C.is_computed = 1 AND C.object_id = T.object_id AND T.name = @lTableName

  SELECT @lExcludeColumns = COALESCE(@lExcludeColumns + ''''',''''', '') + C.name FROM sys.columns C, Sys.tables T WHERE C.is_computed = 1 AND C.object_id = T.object_id AND T.name = @lTableName

  SET @lExcludeColumns = '''''''' + @lExcludeColumns + ''''''''

  print 'Excluded Columns ' + @lExcludeColumns

  -- Inserting insert statement into temp table.

  INSERT INTO #TempTransactionData

  EXEC sp_generate_inserts @lTableName, @from = @lFromQuery, @ommit_select_statement = 1, @cols_to_exclude = @lExcludeColumns

END

-- Delete table entry from list, for which insert statement generated.

DELETE FROM #TransactionTable WHERE TABLE_NAME = @lTableName

END

-- Return result after removing GO from the end.

Select Len(InsertQuery) As 'Length', SUBSTRING(InsertQuery, 0, Len(InsertQuery) - 1) InsertQuery From #TempTransactionData

END