UFN_MERCHANDISEPRODUCTINSTANCE_OPTIONSUNIQUE

Checks to see if product instance options are unique for product.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode	Description
@MERCHANDISEPRODUCTINSTANCEID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_MERCHANDISEPRODUCTINSTANCE_OPTIONSUNIQUE(@MERCHANDISEPRODUCTINSTANCEID uniqueidentifier)
returns bit
as begin
    declare @NOMATCHFOUND bit
  declare @OPTIONCOUNT integer
  declare @MATCHCOUNT integer

  select
    @OPTIONCOUNT = count(*)
  from
    dbo.MERCHANDISEPRODUCTINSTANCE inner join dbo.MERCHANDISEPRODUCTOPTION on
      MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCTOPTION.MERCHANDISEPRODUCTID
  where
    MERCHANDISEPRODUCTINSTANCE.ID = @MERCHANDISEPRODUCTINSTANCEID

  if not exists(
      select 
        MERCHANDISEPRODUCTINSTANCEOPTIONVALUEMATCH.MERCHANDISEPRODUCTINSTANCEID    
      from
        dbo.MERCHANDISEPRODUCTINSTANCE inner join MERCHANDISEPRODUCTINSTANCEOPTIONVALUE on
          MERCHANDISEPRODUCTINSTANCE.ID = MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTINSTANCEID
        inner join dbo.MERCHANDISEPRODUCTINSTANCEOPTIONVALUE MERCHANDISEPRODUCTINSTANCEOPTIONVALUEMATCH on
          MERCHANDISEPRODUCTINSTANCEOPTIONVALUE.MERCHANDISEPRODUCTOPTIONVALUEID = MERCHANDISEPRODUCTINSTANCEOPTIONVALUEMATCH.MERCHANDISEPRODUCTOPTIONVALUEID
      where
        MERCHANDISEPRODUCTINSTANCE.ID = @MERCHANDISEPRODUCTINSTANCEID and
        MERCHANDISEPRODUCTINSTANCEOPTIONVALUEMATCH.MERCHANDISEPRODUCTINSTANCEID <> @MERCHANDISEPRODUCTINSTANCEID
      group by
        MERCHANDISEPRODUCTINSTANCEOPTIONVALUEMATCH.MERCHANDISEPRODUCTINSTANCEID
      having
        count(*) = @OPTIONCOUNT)
    begin
      set @NOMATCHFOUND = 1
    end
  else
    begin
      set @NOMATCHFOUND = 0
    end

    return @NOMATCHFOUND
end