SQL syntax problem (multiple selects)

Question

I am having problems retrieving accurate data values with my stored proc query below:

    CREATE PROCEDURE usp_InvoiceErrorLog
    @RecID int
AS

DECLARE @ErrorString as varchar(1000),
        @ErrorCode as int;

Select @ErrorCode = ErrorCode from tbl_AcctRecv_WebRpt Where RecID = @RecID;
IF NOT(@ErrorCode = NULL)
    Begin
        Select @ErrorString = ErrorDesc from tbl_ErrDesc Where ErrorCode = @ErrorCode
    End

Select  RecID, VendorNum, VendorName, InvNum, InvTotal, (SELECT CONVERT(VARCHAR(11), InvDate, 106) AS [DD MON YYYY]) As InvDate,
        TicketRequestor, ErrorCode, @ErrorString as ErrorDesc
    from tbl_AcctRecv_WebRpt Where RecID =  @RecID

The ErrorDesc column (in the final select statement at the bottom) returns a NULL value, when it should return a valid string data.

Any ideas?

Answer 1

Why dont you first query for that row and double check if ErrorDesc actually contains a value:

SELECT ErrorDesc WHERE RecID=@RecID

What does that return? If NULL then there is no problem.

CREATE PROCEDURE usp_InvoiceErrorLog  
@RecID int  
AS

DECLARE @ErrorString as varchar(1000)
DECLARE @ErrorCode as int

Select @ErrorCode = (SELECT ErrorCode from tbl_AcctRecv_WebRpt Where RecID = @RecID) 

IF (@ErrorCode IS NOT NULL) 
Begin 
    --its NOT NULL
    Select @ErrorString = (SELECT ErrorDesc from tbl_ErrDesc Where ErrorCode = @ErrorCode)
End
--optional it IS NULL
ELSE
    BEGIN
       SELECT @ErrorString = (SELECT 'It Doesnt Contain Anything!')
    END

Select 
     RecID, 
     VendorNum, 
     VendorName, 
     InvNum, 
     InvTotal, 
     (SELECT CONVERT(VARCHAR(11), InvDate, 106) AS [DD MON YYYY]) As InvDate, 
     TicketRequestor, 
     ErrorCode, 
     @ErrorString as ErrorDesc 
FROM 
     tbl_AcctRecv_WebRpt 
Where 
     RecID = @RecID
END

This works for me. Look at your if condition: IF NOT(@ErrorCode = NULL) and look at mine IF (@ErrorCode IS NOT NULL). You want to use IS NOT OR IS Rather then = when dealing with NULL

Answer 2

IF NOT(@ErrorCode = NULL) - ALWAYS false(NULL)!

It should be

IF (@ErrorCode IS NOT NULL)