How stored procedure output instead of rows count?

Question

My stored procedure always returns 0. I tried unique data and duplicated but the insert is done with success but the return value is always the same @new_identity = 0

CREATE PROCEDURE [dbo].[spAddAuthor]
    @Author tyAuthor READONLY,
    @new_identity INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- check if the author exists 
    IF NOT EXISTS (SELECT Id_Author FROM dbo.Authors 
                   WHERE (dbo.Authors.Username = (SELECT Username FROM @Author) 
                          OR dbo.Authors.phone = (SELECT phone FROM @Author) 
                          OR dbo.Authors.email = (SELECT email FROM @Author)))
    BEGIN
        INSERT INTO dbo.Authors (Username, sexe, email, phone, address) 
            SELECT [Username], [sexe], [email], [phone], [address] 
            FROM @Author 

        -- output the new row 
        SELECT @new_identity = @@IDENTITY;
    END
    ELSE
    BEGIN
        -- get the author Id if already exists
        SELECT @new_identity = (SELECT TOP 1 Id_Author 
                                FROM dbo.Authors 
                                WHERE (dbo.Authors.Username = (SELECT Username FROM @Author) 
                                       OR dbo.Authors.phone = (SELECT phone FROM @Author) 
                                       OR dbo.Authors.email = (SELECT email FROM @Author)))
     END
 END

I would recommend to use SCOPE_IDENTITY() instead of anything else (like @@IDENTITY to grab the newly inserted identity value. See this blog post for an explanation as to WHY. Also - are you sure that your table Authors does in fact have an INT IDENTITY column at all? — marc_s, Commented May 12, 2021 at 19:40
Username = (SELECT Username FROM @Author) is going to fail if there is more than one row in the TVP, you should change = to IN. Or better, do it as a proper join JOIN @Author tvp ON tvp.Username = a.Username OR tvp.phone = a.phone OR tvp.email = a.email) and use table aliases to make your code more readable. — Charlieface, Commented May 12, 2021 at 20:46
Username is unique in the table @charlieface. And thanks for the advice — Ahmed Laoun, Commented May 12, 2021 at 21:21
I tried scope_identety and the result are the same. But my gole is to insert new row if not exists and return the id if the new row or return the id. If the row alredy exists can scope_identety get the id of the existing row ?. Sorry for my english — Ahmed Laoun, Commented May 12, 2021 at 21:22
Any case you need to return multiple IDs because your TVP may have multiple rows. So instead of returning a single value in a parameter, change your INSERT and UPDATE to include OUTPUT inserted.AuthorID — Charlieface, Commented May 12, 2021 at 21:33

Ahmed Laoun · Accepted Answer · 2021-05-12 22:13:30Z

0

I found that in the declaration of the parameters I put null beside the output and that what caused the problem.

@new_identity INT = NULL OUTPUT

but I don't understand why, I thought the 'null' was like the default value, or when you try to make the parameter optional you add null as default value. can someone explain, please?

answered May 12, 2021 at 22:13

Ahmed Laoun

154 bronze badges

Add a comment |

Collectives™ on Stack Overflow

How stored procedure output instead of rows count?

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related