why this behaviour with int in SQL Server 2005

Question

I have a requirement in which i need to see whether that key already exists. If it already exists update the data based on the key else just insert new data.

The approach which i took is

DECLARE @Tracking_Id INT
SELECT  @Tracking_Id = Tracking_Id
        FROM    DOCUMENT_TRACKING
        WHERE   Secondary_Document_Id = @Secondary_Document_Id
                AND primary_Document_Id = @Primary_Document_Id 

        IF ( @Tracking_Id = 0 ) 
            BEGIN                           
                INSERT  INTO DOCUMENT_TRACKING
                        (
                          Primary_Document_Id,
                          Secondary_Document_Id,
                          Tracking_Result,
                          Comment,
                          Created_By,
                          Created_Dt,
                          Updated_By,
                          Updated_Dt
                        )
                VALUES  (
                          @Primary_Document_Id,
                          @Secondary_Document_Id,
                          @TrackingResult,
                          @Comments,
                          @User_ID,
                          GETDATE(),
                          @User_ID,
                          GETDATE()
                        )      
            END    

        ELSE 
            BEGIN                  
                SELECT  @Tracking_Id = Tracking_Id
                FROM    DOCUMENT_TRACKING
                WHERE   Secondary_Document_Id = @Secondary_Document_Id
                        AND primary_Document_Id = @Primary_Document_Id   
                UPDATE  DOCUMENT_TRACKING
                SET     tracking_result = @TrackingResult,
                        Comment = @Comments,
                        Updated_By = @User_ID,
                        Updated_Dt = GETDATE()
                WHERE   Tracking_Id = @Tracking_Id        
            END

Now when there is no row corresponding to my condition in database,either @Tracking_Id has to be 0 or '' or null but it shows nothing when i compare it with any of these things.

I donot want to use count approach and then compare with 0 or greater than that. How can i deal with this scenerio.

Answer 1

you can use

IF @@ROWCOUNT = 0

to see the rows returned by a select.

if you are comparing with null that way

IF @var = NULL

will always be false, you should compare like:

IF @var IS NULL

Answer 2

Try changing the if statement to

IF ( ISNULL(@Tracking_Id,0) = 0 )

When there are no items found, @Tracking_Id is null, and does not compare to 0 so it will go into the else.

Answer 3

I think you should replace

 IF ( @Tracking_Id = 0 )

with

 IF ( @Tracking_Id IS NULL)

Or Use

IF EXISTS(SELECT  @Tracking_Id = Tracking_Id
    FROM    DOCUMENT_TRACKING
    WHERE   Secondary_Document_Id = @Secondary_Document_Id
            AND primary_Document_Id = @Primary_Document_Id)
BEGIN
    UPDATE CODE HERE
END
ELSE
BEGIN
    INSERTION CODE HERE
END

Or another alternative would be as follows

UPDATE Statement
IF @@ROWCOUNT = 0
BEGIN
  INSERTION CODE
END

The idea is to systematically apply use an UPDATE statement. If the row exists the @@rowcount will be 1.

Answer 4

I would predict that it is returning NULL rather than zero - you could adjust your query here:

SELECT  @Tracking_Id = ISNULL(Tracking_Id, 0)
        FROM    DOCUMENT_TRACKING
        WHERE   Secondary_Document_Id = @Secondary_Document_Id
                AND primary_Document_Id = @Primary_Document_Id

Answer 5

Query like: "SELECT @variable = field from table" does not clear (nulls) @variable when @variable is not null and there is no records in table. It can sometimes lead to hard-to-find problems I would recommend using: SET @Tracking_Id = (select Tracking_Id FROM DOCUMENT_TRACKING WHERE Secondary_Document_Id = @Secondary_Document_Id AND primary_Document_Id = @Primary_Document_Id)

or use "IF EXISTS(...)" like Raj wrote.

Try this:

-- drop table test

create table test (id integer)

insert into test values(1)

insert into test values(2)

declare @i integer set @i= 123

select @i = id from test where id = 3

select @i --<-- @i=123 - still (WOW :))

set @i = (select /top 1/ id from test where id= 3)

select @i --<-- now @I=NULL, it's OK, You can test for NULL

set @i = (select /top 1/ id from test where id= 1)

select @i --<-- now @I=1

Regards.

Answer 6

IF EXISTS(SELECT  1
    FROM    DOCUMENT_TRACKING
    WHERE   Secondary_Document_Id = @Secondary_Document_Id
            AND primary_Document_Id = @Primary_Document_Id)
BEGIN

SELECT  @Tracking_Id = Tracking_Id
    FROM    DOCUMENT_TRACKING
    WHERE   Secondary_Document_Id = @Secondary_Document_Id
            AND primary_Document_Id = @Primary_Document_Id

update here...
END
ELSE
BEGIN

insert here

END

Answer 7

    if (select count(Tracking_Id) from DOCUMENT_TRACKING where Secondary_Document_Id=@Secondary_Document_Id and primary_Document_Id=@Primary_Document_Id and Tracking_id>0)=0
begin

end
else
begin

end

essentially gets a count of how many records the query returns and then compares to see if it's 0. the >0 makes sure that the Tracking_id isn't '' or null