Database Challenges

Use the following TSQL template to do the basic testing of your TSQL Challenge 21 solutions.

/***********************************************************************
 ---------------------------------------------- 
 BASIC 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 21) 
    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. 

 Revision History:
 Rev 00 - 21 March 2010 - Jacob Sebastian	- Initial Release
***********************************************************************/
/*
	Create a wrapper procedure around the solution
*/
IF OBJECT_ID('tempdb..#TestSolution') IS NOT NULL BEGIN
	DROP PROCEDURE #TestSolution
END
GO

CREATE PROCEDURE #TestSolution AS
BEGIN

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t
SELECT	1,--> 000 007 059
' _  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|
                           
' UNION 
SELECT  2, --> 490 067 715
'    _  _  _  _  _  _     _ 
|_||_|| || ||_   |  |  ||_ 
  | _||_||_||_|  |  |  | _|
                           
' UNION
SELECT	3, --> 680 X68 279
' _  _  _     _  _  _  _  _ 
|_ |_|| || ||_ |_| _|  ||_|
|_||_||_||_||_||_||_   | _|
                           
' UNION
SELECT	4, --> 490 867 716
'    _  _  _  _  _  _     _ 
|_||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
'  UNION
SELECT	5, --> X90 867 716
'    _  _  _  _  _  _     _ 
| ||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
' 
UNION 
SELECT 6, --> 012 345 678
' _     _  _     _  _  _  _ 
| |  | _| _||_||_ |_   ||_|
|_|  ||_  _|  | _||_|  ||_|
                           
' 

;
/* Insert your query below */

/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	ID INT,
	Value VARCHAR(50)
)

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

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	ID INT,
	Value VARCHAR(50)
)

INSERT INTO @z(id, value) SELECT 1,'000007059'
INSERT INTO @z(id, value) SELECT 2,'490067715'
INSERT INTO @z(id, value) SELECT 3,'680X68279'
INSERT INTO @z(id, value) SELECT 4,'490867716'
INSERT INTO @z(id, value) SELECT 5,'X90867716'
INSERT INTO @z(id, value) SELECT 6,'012345678'

-- 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!'
END ELSE BEGIN
  PRINT 'Congratulations...Passed!'
END