1

I'm developing a database with SQLServer 2012 SP2.

I have these tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [...],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [Created]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

I also have this user type:

CREATE TYPE [dbo].[SerialList]
AS TABLE
(
    Serial nvarchar(20)
);

I use this type as a parameter for a Stored Procedure. I use it to pass a list of Serials.

The stored procedure prototype is:

CREATE PROCEDURE [dbo].[AddSerials]
      @serials as dbo.SerialList READONLY
    , @parentCode nvarchar(20)
    , @isAuto bit

I need to loop @serials to check if all of them are in AggregationChildren table. I know that I can do it using a CURSOR but I wondering if I can do it using something faster than a CURSOR that need to loop over all of the @serials rows.

If need something than return 0 if one (or more) of the Serial in @serials don't exist on AggregationChildren table.

Any idea?

0

Assuming my reading of the schema is correct, this should do what you want:

create procedure dbo.AddSerials (
      @serials as dbo.SerialList readonly
    , @parentCode nvarchar(20)
    , @isAuto bit
    ) as
begin;
  set nocount on;
  declare @AggregationId int;
  select @AggregationId = CodeId
    from dbo.[Code] c
    where c.Serial = @parentCode;
  if @AggregationId is null
    begin;
      -- if the parent doesn't exist, children don't exist
      return 0;
    end;
  -- parent exists, check children
  if exists (
    select 1
      from @serials s
      where not exists (
        select 1
          from dbo.AggregationChildren as ac
            inner join dbo.[Code] as acc
              on ac.AggregationChildrenId = acc.CodeId
                and ac.AggregationId = @AggregationId
          where acc.Serial = s.Serial
        )
    )
    begin;
      -- at least one @serials does not exist under parent
      return 0;
    end;
    else 
    begin;
      -- all of the @serials exist under parent
      return 1;
    end;
end;

If you want to skip checking the parent code, this would also work:

create procedure dbo.AddSerials (
      @serials as dbo.SerialList readonly
    , @parentCode nvarchar(20)
    , @isAuto bit
    ) as
begin;
  set nocount on;
  if exists (
    select 1
      from @serials s
      where not exists (
        select 1
          from dbo.[Code] as ac
            inner join dbo.Aggregation as a
              on ac.CodeId = a.AggregationId
                and ac.Serial = @parentCode
            inner join dbo.AggregationChildren as ac
              on a.AggregationId = ac.AggregationId
            inner join dbo.[Code] as acc
              on ac.AggregationChildrenId = acc.CodeId
          where acc.Serial = s.Serial
        )
    )
    begin;
      -- at least one @serials does not exist under parent
      return 0;
    end;
    else 
    begin;
      -- all of the @serials exist under parent
      return 1;
    end;
end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.