iVend Database Database : InvGetSelectedAttributeProduct Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pHardwareId

In

VarWChar

200

@pProductKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvTempMatrixProductAttribute

Table

Specifies the Temp product attribute for the products

1

usp_RethrowError

Stored Procedure

Rethrow an error to the calling function/application

1

--select * from invproduct

--exec InvGetSelectedAttributeProduct 4234315916, 10000000000029

CREATE PROCEDURE [dbo].[InvGetSelectedAttributeProduct]

(

@pHardwareId NVARCHAR(200),

@pProductKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lCurrentProductNumber INT,

  @lAttributeCount INT,

  @lSelectedQuantity DECIMAL(20, 5)

BEGIN TRY

  CREATE TABLE #TempMatrixAttributes

   (

    [ProductKey] nvarchar(50) NOT NULL,

    [Quantity] DECIMAL(20, 5) NOT NULL

   )

  DECLARE ProductListCursor CURSOR FOR

  SELECT ProductNumber, COUNT(ProductNumber), Quantity

  FROM InvTempMatrixProductAttribute

  WHERE  HardwareId = @pHardwareId

  GROUP BY ProductNumber,  Quantity

  OPEN ProductListCursor

  FETCH NEXT FROM  ProductListCursor INTO @lCurrentProductNumber, @lAttributeCount, @lSelectedQuantity

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #TempMatrixAttributes(ProductKey, Quantity)

  SELECT B.ProductKey, @lSelectedQuantity

  FROM InvTempMatrixProductAttribute A, InvProductAttribute B, InvProduct C

  Where A.AttributeDetailKey = B.AttributeDetailKey

  And  B.ProductKey = C.ProductKey

  And  C.ParentMartixProductKey = @pProductKey

  And  A.ProductNumber = @lCurrentProductNumber

  AND  A.HardwareId = @pHardwareId

  GROUP BY B.ProductKey

  HAVING COUNT(B.ProductKey) = @lAttributeCount

  FETCH NEXT FROM ProductListCursor INTO @lCurrentProductNumber, @lAttributeCount, @lSelectedQuantity

  END

  CLOSE ProductListCursor

  DEALLOCATE ProductListCursor

  SELECT * FROM #TempMatrixAttributes

END TRY

BEGIN CATCH

  CLOSE ProductListCursor

  DEALLOCATE ProductListCursor

  Execute usp_RethrowError

END CATCH

END