Database Challenges

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

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

 2. Insert your query (solution for TSQL Challenge 14) 
    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 - 03 April 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 (Data VARCHAR(40))
	INSERT INTO @t (Data)
	SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97' UNION ALL
	SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1' UNION ALL
	SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C' UNION ALL
	SELECT '5AAF477C-274D-400D-9067-035968F33B19' UNION ALL
	SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE' UNION ALL
	SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
	UNION ALL
	SELECT '07777770-BAAB-CDDC-EFFE-553553553553' UNION ALL
	SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF' UNION ALL
	SELECT '00000000-0000-0000-0000-000000000000' UNION ALL
	SELECT '01234567-89AB-CDEF-0123-4567890ABCDE' UNION ALL
	SELECT '33333331-FEEE-FEEE-FEEE-1234567890AB' UNION ALL
	SELECT '00000001-2223-4445-6667-888888888889' UNION ALL
	SELECT '01010101-0101-0101-0101-777777777701' UNION ALL
	SELECT 'EDDDDDDD-1234-5678-9876-ABC555555555' UNION ALL
	SELECT 'FEDCBA98-7654-3219-ABCD-0123456789AA' 
	;
	/* Insert your query below */

	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
    pkid INT IDENTITY,
    data VARCHAR(40),
    [char] CHAR(1),
    pos INT,
    [len] INT
)

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

DECLARE @Z TABLE(
    pkid INT IDENTITY,
    data VARCHAR(40),
    [char] CHAR(1),
    pos INT,
    [len] INT
)

INSERT @Z (data, [char], pos, [len])
SELECT '00000000-0000-0000-0000-000000000000','0',  1,  8 UNION ALL
SELECT '00000000-0000-0000-0000-000000000000','0', 10,  4 UNION ALL
SELECT '00000000-0000-0000-0000-000000000000','0', 15,  4 UNION ALL
SELECT '00000000-0000-0000-0000-000000000000','0', 20,  4 UNION ALL
SELECT '00000000-0000-0000-0000-000000000000','0', 25, 12 UNION ALL
SELECT '00000001-2223-4445-6667-888888888889','0',  1,  7 UNION ALL
SELECT '00000001-2223-4445-6667-888888888889','2', 10,  3 UNION ALL
SELECT '00000001-2223-4445-6667-888888888889','4', 15,  3 UNION ALL
SELECT '00000001-2223-4445-6667-888888888889','6', 20,  3 UNION ALL
SELECT '00000001-2223-4445-6667-888888888889','8', 25, 11 UNION ALL
SELECT '01010101-0101-0101-0101-777777777701','7', 25, 10 UNION ALL
SELECT 'EDDDDDDD-1234-5678-9876-ABC555555555','D',  2,  7 UNION ALL
SELECT 'EDDDDDDD-1234-5678-9876-ABC555555555','5', 28,  9 UNION ALL
SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF','A',  1,  8 UNION ALL
SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF','B', 10,  4 UNION ALL
SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF','C', 15,  4 UNION ALL
SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF','D', 20,  4 UNION ALL
SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF','E', 25,  6 UNION ALL
SELECT 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEFFFFFF','F', 31,  6 UNION ALL
SELECT '33333331-FEEE-FEEE-FEEE-1234567890AB','3',  1,  7 UNION ALL
SELECT '33333331-FEEE-FEEE-FEEE-1234567890AB','E', 11,  3 UNION ALL
SELECT '33333331-FEEE-FEEE-FEEE-1234567890AB','E', 16,  3 UNION ALL
SELECT '33333331-FEEE-FEEE-FEEE-1234567890AB','E', 21,  3 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','7',  2,  6 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','A', 11,  2 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','D', 16,  2 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','F', 21,  2 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','5', 25,  2 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','5', 28,  2 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','5', 31,  2 UNION ALL
SELECT '07777770-BAAB-CDDC-EFFE-553553553553','5', 34,  2 UNION ALL
SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888','B', 20,  2 UNION ALL
SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888','8', 32,  5 UNION ALL
SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C','7', 10,  4 UNION ALL
SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C','7', 34,  2 UNION ALL
SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97','9',  1,  3 UNION ALL
SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97','1', 11,  2 UNION ALL
SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97','E', 17,  2 UNION ALL
SELECT '5AAF477C-274D-400D-9067-035968F33B19','A',  2,  2 UNION ALL
SELECT '5AAF477C-274D-400D-9067-035968F33B19','7',  6,  2 UNION ALL
SELECT '5AAF477C-274D-400D-9067-035968F33B19','0', 16,  2 UNION ALL
SELECT '5AAF477C-274D-400D-9067-035968F33B19','3', 32,  2 UNION ALL
SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE','4', 15,  2 UNION ALL
SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE','E', 33,  2 UNION ALL
SELECT 'FEDCBA98-7654-3219-ABCD-0123456789AA','A', 35,  2

-- 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