CODEWITHSUNDEEP
sqlsql-serverstored-procedures
NullReference
NullReference

Reputation: 4484

How to use a sql table-valued parameter to filter results?

I'm trying to create a stored procedure that uses a table-valued parameter to filter imported names. We get a list of names imported and need to return a list of names that either do not exist in the database or they've already received a message of a specific type.

The part I'm having difficult with is how to create the joins. A left outer join with a IS NULL check would work for a single table check but I'm not sure about two tables. Any help would be greatly appreciated. Thanks!

I'm using MS SQL 2012. Tables with their columns:

Recipients: 
id | name 

SendResults:
id | recipientid | type

Tes

CREATE PROCEDURE [dbo].[usp_RecipientsSendCheck]
(
     @Type int 
    ,@RecipientsImports dbo.RecipientsImport READONLY
)
AS
BEGIN

    SELECT i.Name FROM @RecipientsImports i

    LEFT JOIN Recipients r
    ON i.Name = r.Name

    LEFT JOIN SendResults s
    ON s.RecipientId = r.id 

    WHERE r.Name IS NULL OR Type <> @Type

END

User defined table type

CREATE TYPE [dbo].[RecipientsImport] AS TABLE(
    [Name] [nchar](10) NOT NULL
)

Upvotes: 3

Views: 1597

Answers (1)

roman
roman

Reputation: 117380

Something like this:

create procedure [dbo].[usp_RecipientsSendCheck]
(
    @Type int,
    @RecipientsImports dbo.RecipientsImport READONLY
)
as
begin
    select i.Name
    from @RecipientsImports as i
        left outer join Recipients as r on r.Name = i.Name
        left outer join SendResults as s on s.RecipientId = r.id and s.[Type] = @Type
    where
        r.Name is null or
        s.id is not null
end

Upvotes: 4

Related Questions