SCOPE_IDENTITY() return NULL ?

Question

I am using SQL Server 2008 express, below is the SP, who return

(0 row(s) affected)

Msg 515, Level 16, State 2, Procedure sp_AddCarrierFees,

Line 21 Cannot insert the value NULL into column 'attribute_value_id', table 'MyDevSystem.dbo.shipping_fees';

column does not allow nulls. INSERT fails. The statement has been terminated.

(1 row(s) affected)

And this is the SP :

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_AddCarrierFees]
    @carrier_id INT,
    @zone_id INT,
    @attribute_value_id INT,
    @attribute_title varchar(20),
    @fees decimal(6,2)
AS
BEGIN
    if @attribute_value_id = 0 begin 
        insert into shipping_attribute_value (attribute_id,attribute_value,sort_order) 
        select attribute_id, @fees,0 from shipping_attribute where carrier_id=@carrier_id and attribute_title=@attribute_title; 

        declare @NewID int;
        set @NewID = SCOPE_IDENTITY(); 
        print @NewID;

        insert into shipping_fees (zone_id, attribute_value_id) values (@zone_id, @NewID); 
    end 
    else 
    begin 
        update shipping_attribute_value set attribute_value=@fees where attribute_value_id=@attribute_value_id;
    end
END

Any people know why? I have read many post in StackOverFlow, but still not find solution.Somebody say use @@IDENTITY or IDENT_CURRENT instead, but it possible got the identity who made by other users.

FIXED:I found the reason, because the first insert statement is fail, so why return (0 row(s) affected) , after i fix that insert statement,it works now. thanks you for everyone.

Answer 1

First verify that the shipping_attribute_value actually has an identity column. Without an identity column, scope_identity() doesn't work.

EDIT: I missed that the insert is actually using the select, but marc_s noticed it :) *grabs coffee*

How about doing a separate select just to see the output:

select attribute_id, @fees,0 from shipping_attribute 
       where carrier_id=@carrier_id and attribute_title=@attribute_title; 

insert into shipping_attribute_value (attribute_id,attribute_value,sort_order) 
    select attribute_id, @fees,0 from shipping_attribute 
    where carrier_id=@carrier_id and attribute_title=@attribute_title; 

If no rows are inserted, scope_identity() should be null :)

Answer 2

Re:Andomar

I try

    declare @NewID int;
    insert into shipping_attribute_value (attribute_id,attribute_value,sort_order) 
    set @NewID = SCOPE_IDENTITY(); 
    select attribute_id, @fees,0 from shipping_attribute where carrier_id=@carrier_id and attribute_title=@attribute_title; 

But it error :

Incorrect syntax near the keyword 'set'.