Stored procedure - Cursor - assign a value to record field and select at the end of the cursor

Question

I'm passing a list of contact objects (List<Contact>) to a SQL Server stored procedure from C#:

and to capture this list I have used a user-defined table type in SQL Server like below:

Now with in my stored procedure I need to do below things.

1. Loop (cursor) though contacts list passed to stored procedure

2. If tblContact table in the database has a record with the current email address (of current contact record/iteration), it needs to gets that tblContact records ContactId and assign it to the current Contact iteration record; else insert a new record to tblContact with that email address (which does not exist) and assign its contactId value to current contact record/iterations tblContact field (as contactId is primary key of TblContact, I can do

   SET @contactId = (SELECT SCOPE_IDENTITY()); 
   

   ).

3. Finally select the modified contacts records

I need some help with implementing the 2nd step listed above, and this what I have written so far,

ALTER PROCEDURE [dbo].[UpdateMailjetDetails] 
    @contacts Contact READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. Loop (cursor) though contacts list passed to stored procedure
    DECLARE @contactEmail varchar(1000);

    DECLARE cur CURSOR FOR 
         SELECT Email FROM @contacts

    OPEN cur    
    FETCH NEXT FROM cur INTO @contactEmail; 

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        DECLARE @contactId INT = (SELECT TOP(1) ContactId 
                                  FROM tblContact 
                                  WHERE EmailAddress = @contactEmail);

        IF (@contactId != '')
        BEGIN           
            SELECT 'Exists - ' + CONVERT(VARCHAR, @contactId);

        -- 2 assign contact Id to current contact.TblContactId
    END
    ELSE
    BEGIN
        INSERT INTO tblContact (EmailAddress, DateCreated) 
        VALUES (@contactEmail, GETDATE()); 

        SET @contactId = (SELECT SCOPE_IDENTITY()); 
        -- 2 new @contactId value to current contact.TblContactId 
    END

    FETCH NEXT FROM cur INTO @contactEmail;
END

CLOSE cur    
DEALLOCATE cur

-- 3
SELECT * FROM @contacts
END

And this is how I'm executing the stored procedure:

DECLARE @return_value int
DECLARE @input Contact;

INSERT INTO @input (Email, [Name], TblContactId, CompanyId, TblContactCompanyId) 
VALUES ('a@a.com', 'a', null, null, null),
       ('b@a.com', 'b', null, 1, 1),
       ('a@gmail.com', 'aa', null, 1, 1),
       ('b@hotmail.com', 'bb', null, 1, 1)


EXEC @return_value = [dbo].[UpdateMailjetDetails]
                     @contacts = @input

SELECT 'Return Value' = @return_value
GO

So, as above after executing the stored procedure, it need to print all contact records with it TblContactId (existing, or newly inserted) values.

Any help or guidance on this much appreciated. Thanks.

Answer 1

If I understand correctly, you want to take a list of input email addresses, insert any into the target table if that email address does not already exist, and then output the original list of addresses with their corresponding data in the target table? You don't need a cursor at all, the following should do what you need and will probably perform better:

CREATE PROC dbo.UpdateMailjetDetails (
    @contacts dbo.Contact READONLY
)
AS

INSERT INTO dbo.tblContact (EmailAddress, DateCreated)
SELECT c.Email, SYSDATETIME()
FROM @contacts c
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.tblContact t
    WHERE t.EmailAddress = c.Email
);

SELECT c.Email, t.[Name], t.tblContactId, c.CompanyId, t.CompanyId AS tblContactCompanyId
FROM @contacts c
    LEFT OUTER JOIN dbo.tblContact t ON t.EmailAddress = c.Email;

Some of my assumptions may be slightly off here, but this should serve as a starting point. Note that if you don't have a unique constraint on the email address column in the target table, then the results at the end could include more rows than you originally supplied, because there could be more than one matching row. Note that if you want to perform other operations, such as an update if the row exists, then the insert statement can be replaced with a merge statement.