Database Challenges

/***********************************************************************
 ----------------------------------------------
 LOGIC Testing Sandbox for TSQL Challenge 21
 Copyright © beyondrelational.com
 ----------------------------------------------

 Notes:
 1. Copy this template and paste in your SSMS Query editor

 2. Insert your query (solution for TSQL Challenge 25)
    in the Section between "Insert your query below"
    and "Insert your query above"

 3. Run the whole batch. If you see
    "Invalid Results...Failed!" getting printed in output
    window, there is something wrong in the query.
    (There may be some basic info as to what is missing/extra as well)

 Revision History:
 Rev 00 - 15 April 2010 - Jesse Roberge   
	- Initial Release 
 Rev 01 - 19 April 2010 - David Barbarin 
	- Added information about lines with invalid results 
             
***********************************************************************/
/*
	Create a wrapper procedure around the solution
*/
IF OBJECT_ID('tempdb.dbo.#TestSolution') IS NOT NULL BEGIN
	DROP PROCEDURE #TestSolution
END
GO

CREATE PROCEDURE #TestSolution AS
BEGIN

DECLARE @t TABLE (AutoID Int IDENTITY(1,1), Id int, ScanNumber NVARCHAR(250))

INSERT INTO @t
SELECT  10,--> 000007059000007059 
' _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _||_||_||_||_||_|  ||_| _| _|
                                                      
' UNION ALL
SELECT  11,--> 0123456789 
' _     _  _     _  _  _  _  _ 
| |  | _| _||_||_ |_   ||_||_|
|_|  ||_  _|  | _||_|  ||_| _|
' UNION ALL
SELECT  12,-->  012 34 
'    _     _     _    
   | |  | _|    _||_|
   |_|  ||_     _|  |
                     
' UNION ALL
SELECT  13,--> 01234 
' _     _  _    
| |  | _| _||_|
|_|  ||_  _|  |
|_||_||_||_||_|                                                      
' UNION ALL
SELECT  14,-->  7XXXX 7 
' _                 _ 
  ||_||_|| ||_|     |
  || | ||||| |      |
                    
' UNION ALL
SELECT  15,--> 00000705970590000070597059 
' _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|  || ||_ |_|| || || || || |  || ||_ |_|  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|  ||_| _| _||_||_||_||_||_|  ||_| _| _|  ||_| _| _|
';

/* Insert your query below */

/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/
DECLARE @x TABLE(
	AutoID INT IDENTITY PRIMARY KEY CLUSTERED,
	ID Int,
	ScanNumber VarChar(250)
)

-- Execute the code and
INSERT @x EXEC #TestSolution

-- Table to hold the expected results
DECLARE @z TABLE(
	AutoID INT IDENTITY PRIMARY KEY CLUSTERED,
	Id int,
	ScanNumber NVARCHAR(250)
)

INSERT INTO @z([ID],ScanNumber) 
SELECT * FROM (
	SELECT NULL AS [ID], NULL AS [Digit] WHERE 1=2 UNION ALL
	SELECT 10, '000007059000007059' UNION ALL
	SELECT 11, '0123456789' UNION ALL
	SELECT 12, ' 012 34 ' UNION ALL
	SELECT 13, '01234' UNION ALL
	SELECT 14, '7XXXX 7' UNION ALL
	SELECT 15, '00000705970590000070597059' UNION ALL
	SELECT NULL AS [ID], NULL AS [Digit] WHERE 1=2
) AS ScriptedData;

-- Match the output with expected result.  
IF EXISTS(  
	(SELECT * FROM @x EXCEPT SELECT * FROM @z)  
	UNION ALL 
	(SELECT * FROM @z EXCEPT SELECT * FROM @x)
)  
BEGIN 
	PRINT 'Invalid Results...Failed!';

	-- Lines with invalid results
	SELECT *,'Extra' AS Extra_Missing FROM @x
	EXCEPT
	SELECT *,'Extra' AS Extra_Missing FROM @z
	UNION ALL
	SELECT *,'Missing' AS Extra_Missing FROM @z
	EXCEPT
	SELECT *,'Missing' AS Extra_Missing FROM @x;
END ELSE BEGIN 
	PRINT 'Congratulations...Passed!' 
END;