How to return value 1 stored procedure

Question

I have written a stored procedure for inserting name and email address:

CREATE PROCEDURE [dbo].[usp_Referral_Email]
    @user_key varchar(36),
    @name nvarchar(100),    
    @email nvarchar(500),
    @result int output
AS
BEGIN
    DECLARE @username Nvarchar(500)
    DECLARE @useremail Nvarchar(500)
    DECLARE @CusrsorID CURSOR

    SET @CusrsorID = CURSOR FOR
        SELECT Value,Value1
        FROM ufn_split_string(@name,@email) 

    OPEN @CusrsorID

    FETCH NEXT FROM @CusrsorID INTO @username, @useremail

    WHILE @@FETCH_STATUS = 0
    BEGIN
        declare @user nvarchar(36)

        begin try
        begin transaction trans_Referral_Email      

            IF NOT EXISTS (SELECT 1 FROM dbo.C_User_Credentials 
                           WHERE email = @useremail)
            BEGIN
                IF NOT EXISTS (SELECT 1 FROM dbo.Referral_Email 
                               WHERE R_Email = @useremail)
                BEGIN
                    INSERT INTO dbo.Referral_Email (CFK_C_UP_key, R_Name, R_Email)
                    VALUES (@user_key, @username, @useremail)               

                    SET @result = 1
                END
                ELSE
                BEGIN
                    SET @result = 0
                END
            END
            ELSE
            BEGIN
                SET @result = 0
            END

            COMMIT transaction trans_Referral_Email
        end try
        begin catch
            rollback transaction trans_Referral_Email
            set @result=ERROR_MESSAGE()
        end catch


        FETCH NEXT FROM @CusrsorID INTO @username, @useremail
    END

    CLOSE @CusrsorID
    DEALLOCATE @CusrsorID
END

As a example I will pass value

1. @name = ramesh,suresh,rahul
2. @email = ramesh@gmail.com,suresh@gmail.com,rahul@gmail.com

before inserting we checking condition email address are exists or not, suppose email address is exist it will not insert into the table. Now my problem is i will explain through example. ramesh@gmail.com and suresh@gmail.com are new email address both email address are will insert into the table so return value is 1 rahul@gmail.com already exist in table so it will insert into the table so return value will be 0 and output @return value will be 0 but we have inserted 2 email address so i need @return value should be 1 as out put.

So my question is if at any place of email address is insert into the table if one email address also insert output should be @return=1

Answer 1

What you need is known as a "latch" (archaic) or as a flag variable, and is pretty common.

A flag variable (in this case, @result) should be initialized outside the loop and then set when a condition arises (in this case, a record is inserted). The variable should not be touched when any subsequent records are skipped. That way it acts as a sort of an OR gate.

Like this:

CREATE PROCEDURE [dbo].[usp_Referral_Email]
    @user_key varchar(36),
    @name nvarchar(100),    
    @email nvarchar(500),
    @result int output
AS
BEGIN
    DECLARE @username Nvarchar(500)
    DECLARE @useremail Nvarchar(500)
    DECLARE @CusrsorID CURSOR

    SET @CusrsorID = CURSOR FOR
        SELECT Value,Value1
        FROM ufn_split_string(@name,@email) 

    OPEN @CusrsorID

    FETCH NEXT FROM @CusrsorID INTO @username, @useremail
    SET @result = 0  --<--- Will stay 0 until one or more rows are inserted

    WHILE @@FETCH_STATUS = 0
    BEGIN
        declare @user nvarchar(36)

        begin try
        begin transaction trans_Referral_Email      

            IF NOT EXISTS (SELECT 1 FROM dbo.C_User_Credentials 
                           WHERE email = @useremail)
            BEGIN
                IF NOT EXISTS (SELECT 1 FROM dbo.Referral_Email 
                               WHERE R_Email = @useremail)
                BEGIN
                    INSERT INTO dbo.Referral_Email (CFK_C_UP_key, R_Name, R_Email)
                    VALUES (@user_key, @username, @useremail)               

                    SET @result = 1  --<--- Will stay 1 for the rest of its lifespan, even if other rows are not inserted
                END
            END

            COMMIT transaction trans_Referral_Email
        end try
        begin catch
            rollback transaction trans_Referral_Email
            set @result=ERROR_MESSAGE()
        end catch


        FETCH NEXT FROM @CusrsorID INTO @username, @useremail
    END

    CLOSE @CusrsorID
    DEALLOCATE @CusrsorID
END

Notice I've removed a bunch of the ELSE conditions, since you don't need to do anything when a record is skipped.

Answer 2

you are trying to process/insert several users with 1 stored procedure call and you can't use a single output INT field to return back insert status for several users.

Better to split @name and @email parameters at application level and pass to your (modified) stored procedure only a SINGLE pair of name and email. You will then have to call the spT from application level several times for each name/email pair.

If you still want to use a single spT for batch user insert, you will have to record each insert status into a temp table or table variable and then at the spT end you will have to SELECT from that temp table or table variable.

This way at application level you will have a row with status returned for each name/email input pair.

But I personally suggest you actually change your spT to be called once per each name/email pair. It's a better and cleaner approach.

HTH