From a user table check if one of them exists on a table 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?
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;