Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: Display an error message

    Need help with displaying an error message.
    I am comparing two GL columns from two different tables to see if there is a difference between them.
    select LEFT ( gl,8) from table1
    except
    select gl from table2
    and this is working it gives me the difference, what I can't figure out is how to display an error message showing all wrong numbers.

    I tried
    declare @Error varchar
    set @Error =(select LEFT ( gl,8) from table1
    except
    select gl from table2)

    if @Error > 0 print 'Error the data is not correct: ' + @Error

    but this is working only if there is only one non matching record.


    thank you for your help!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Wouldn't you need to declare @Error as a table?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The result set of the comparison could contain tens, hundreds, thousands, ... of differences.
    Like:
    Code:
    abcdefgh
    bcdefghi
    defghij
    efghijk
    ...
    How would you like to have those values, spread over multiple records displayed in one error message?

    Code:
    Error the data is not correct: abcdefgh, bcdefghi, defghij, efghijk, ...
    Try this.
    Code:
    declare @Error varchar(max);
    
    WITH CTE AS(
    select LEFT(gl,8) as field1 from table1
    	except
    select gl as field1 from table2 
    )
    SELECT @Error = STUFF((SELECT ', ' + field1
    			FROM CTE
    			FOR XML PATH('')
                  ), 1, 1, '')
    ;
    
    if len(@Error) > 0 print 'Error the data is not correct: ' + @Error;
    Last edited by Wim; 06-06-13 at 11:28.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2013
    Posts
    46
    Wim thanks a lot it works!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •