Getting Null value Of variable in sql server

Question

Strange situation In a trigger i assign a column value to variable but gives exception while inserting into other table using that variable.

e.g

select @srNO=A.SrNo from A where id=123;
insert into B (SRNO) values (@srNo) // here it gives null

I run above select query in query pane it works fine but in trigger it gives me null

any suggestions

ALTER PROCEDURE ProcessData
@Id decimal(38,0),
@XMLString varchar(1000),
@Phone varchar(20)
AS
DECLARE

@idoc int,
@iError int,
@Serial varchar(15),
@PhoneNumber varchar(15),

BEGIN
COMMIT TRAN

EXEC sp_xml_preparedocument @idoc OUTPUT,@XMLString<br/>

SELECT @iError = @@Error<br/>
         IF @iError = 0<br/>
    BEGIN<br/>

SELECT @Serial = convert(text,[text]) FROM OPENXML (@idoc,'',1) where nodetype = 3 and ParentId = 2

        IF @Serial=Valid <br/>

                      BEGIN<br/>
                            BEGIN TRAN INVALID<br/>
                            begin try <br/>
                            Declare @phoneId decimal(38,0);<br/>
                               SELECT @phoneId = B.phoneId  FROM A
        INNER JOIN B ON A.Id = B.Id WHERE A.PhoneNumber like '%'+@SenderPhone + '%'<br/>

        print @phoneId ; //gives null<br/>

            end try<br/>
            begin catch<br/>
            print Error_Message();<br/>
            end catch<br/>

Answer 1

you should work with sets of rows in triggers, so if multiple rows are affected your code handles all rows. This will only INSERT when the value is not null:

INSERT INTO B (SRNO)
SELECT A.SrNo FROM A where id=123 AND A.SrNo IS NOT NULL

Answer 2

Neo, are you sure, that SELECT SrNo FROM A WHERE id = 123 returns data? I mean, value of @srNo will not change (therefore, remain NULL) if there no records with id = 123

Answer 3

When you eliminate the impossible, whatever remains, however improbable, must be the truth.

The obvious answer is that at the time the trigger fires, SrNo is null or Id 123 does not exist. Is this for an insert trigger and is it the case that you are trying to take something that was just inserted into table A and push it into table B? If so, you should query against the inserted table:

//from an insert trigger on the table `A`
Insert B( SRNO )
Select SRNO
From inserted
Where Id = 123

If this is not the case, then we'd need to see the details of the Trigger itself.

Answer 4

solved it there is some error in xml string reading function e.g in openxml pattern matching

Thanks all of you for help... :)