Transact SQL – Unit Test – Compare Stored Procedure ResultSet

 

Background

As part of our encryption project, we are having to make a lot of changes to scores of Stored Procedures.

Unfortunately, some of them are a bit slow, and we are thus taking the time to optimize some of them.

Knowing me, I will break a lot of things, and so let us see if we can unit test the revision by comparing the results of the previous work against our revised copy.

 

Using Other’s Work

Unlike Paul, I  do not mind building on other’s labor.  And, so took to the .Net and found a gem.

It is  Derek Dieter’s public labor and here it is:

How to Compare Stored Procedure Result Sets
http://sqlserverplanet.com/tsql/compare-stored-procedure-results

Introduction

Basically, Derek created a loop-back Linked Server as a tunnel to each Stored Procedure.  The call is made over OpenQuery and the results are saved in temporary tables.

He could have called the SP without using a select/into, but that means that one loses the flexibility of the schema-less design that makes this so appealing.

Prerequisite

Linked Server

Create Linked Server

Create a linked Server name loopback

Code


USE [master]
GO
 
declare @serverName sysname
 
set @serverName = cast(serverproperty('servername') as sysname)
 
if not exists
(
	select *
	from sys.servers
	where name = N'LOOPBACK'
)
begin
 
	print 'Adding Loopback server ' + isNull(@serverName, '')
 
	EXEC master.dbo.sp_addlinkedserver
			  @server = N'LOOPBACK'
			, @srvproduct=N''
			, @provider=N'SQLOLEDB'
			, @datasrc=@serverName
			, @catalog=N'tempdb'
end

 
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'rpc out', @optvalue=N'true'
 
USE [master]
GO
 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOOPBACK', @locallogin = NULL , @useself = N'True'
GO


Review Linked Server

Code


SELECT
		tblS.[name],
		tblS.[data_source],
		tblS.[catalog],
		tblS.[is_linked],
		tblS.[is_data_access_enabled],
		[status]
			=
			REVERSE
			(
				STUFF
				(
					REVERSE
					(
 
						CASE tblS.is_rpc_out_enabled
							WHEN 1 THEN 'rcp out'
							ELSE ''
						END
						+ ','
						+
						CASE tblS.is_data_access_enabled
							WHEN 1 THEN 'data access enabled'
							ELSE ''
						END
						+ ','
					)
					, 1, 1, ''
				)
 			)
 
FROM sys.servers tblS

 

Output:

linkedServer

Outline

Here is a brief outline of the steps that we will take:

  1. Create Table
  2. Add data
  3. Stored Procedure
    • Create original Stored Procedure ( [unitest].[usp_friendAndfamily_list] )
    • Create original Stored Procedure ( [unitest].[usp_friendAndfamily_list_v2] )
  4. Call each Stored Procedure and cache the results in a separate temp table
  5. Compare results
    • Compare result table metadata
    • Compare result data

LAB

Schema

We will use a specific schema and thus separate this effort from others that are in same database.


use [tempdb]
go

if schema_id('unitest') is null
begin

    exec('create schema [unitest] authorization [dbo]')

end
go

 

Table

Define Table

[unitest].[friendAndfamily]

Create Table [unitest].[friendAndfamily]


USE [tempdb]
GO

IF SCHEMA_ID('unitest') IS NULL
BEGIN

     EXEC ('create schema [unitest] authorization [dbo]')

END
GO

--drop table [unitest].[friendAndfamily]
IF OBJECT_ID('[unitest].[friendAndfamily]') IS NULL
BEGIN

  CREATE TABLE [unitest].[friendAndfamily]
  (

      [id] int NOT NULL IDENTITY (1, 1),
      [lastname] varchar(60) NOT NULL,
      [firstname] varchar(60) NOT NULL,
      [gender] char(1) NOT NULL

    , CONSTRAINT [PK_FriendAndFamily]
       PRIMARY KEY
       (
          [id]
       )

   , CONSTRAINT [ConstraintUniqueLastNameFirstName]
     UNIQUE
     (
        [lastname]
      , [firstname]
    )

  )

END

GO

 

Stored Procedure

[unitest].[usp_friendAndfamily_list]

 


IF SCHEMA_ID('unitest') IS NULL
BEGIN

EXEC ('create schema [unitest] authorization [dbo]')

END
GO

IF OBJECT_ID('[unitest].[usp_friendAndfamily_list]') IS NULL
BEGIN

EXEC ('create procedure [unitest].[usp_friendAndfamily_list] as select 1/0 as [shell] ')

END
GO

ALTER PROCEDURE [unitest].[usp_friendAndfamily_list]
(
	  @lastname varchar(60) = NULL
	, @firstname varchar(60) = NULL
	, @gender char(1) = NULL
)
AS
BEGIN

	SELECT
			  tblFF.[id]
			, tblFF.[firstname]
			, tblFF.[lastname]
			, tblFF.[gender]

	FROM [unitest].[friendAndfamily] tblFF

	WHERE
		(

			    ( tblFF.[lastname] LIKE (ISNULL(@lastname, '') + '%') )

			AND ( tblFF.[firstname] LIKE (ISNULL(@firstname, '') + '%') )

			AND ( tblFF.[gender] LIKE (ISNULL(@gender, '') + '%') )

		)

	UNION

	SELECT

		    [id] = 0
		,   [firstname] = ''
		,   [lastname]  = ''
		,   [gender]    = '1'

END

GO

 

[unitest].[usp_friendAndfamily_list_v2]

 



use [tempdb]
go

if schema_id('unitest') is null
begin

	exec('create schema [unitest] authorization [dbo]')

end
go

if object_id('[unitest].[usp_friendAndfamily_list_v2]') is null
begin

     exec('create procedure [unitest].[usp_friendAndfamily_list_v2] as select 1/0 as [shell] ')

end
go

alter procedure [unitest].[usp_friendAndfamily_list_v2]
(
	  @lastname varchar(60) = null
	, @firstname varchar(60) = null
	, @gender char(1) = null
)
as
begin

	select
		  tblFF.[id]
		, tblFF.[firstname]
		, tblFF.[lastname]
		, tblFF.[gender]

	from [unitest].[friendAndfamily] tblFF

	where

	(

		    (tblFF.[lastname] like ( isNull(@lastname, '') + '%') )

		and (tblFF.[firstname] like ( isNull(@firstname, '') + '%') )

		and (

				[tblFF].[gender]
					=
						case
	
							when (@gender in ('M', 'F')) then @gender
							else [tblFF].[gender]

						end

			)

)

union

	select
		  [id] = 0
		, [firstname] = ''
		, [lastname] = ''
		, [gender]= '2'

end
go


Differences

  1. Gender Defense
    • In the revised SP, if gender is not M or F we return all records
  2. We added a singular record to each Stored Procedure
    • In doing so, we will force at least a lone record difference
    • The difference is in the gender column
      • For original SP, gender will be 1
      • For revised SP, gender will be 2

 

Add Data

 


set nocount on;
go

use [tempdb]
go

truncate table [unitest].[friendAndfamily];

insert into [unitest].[friendAndfamily]
([lastname], [firstname], [gender])
select 'Cassidy', 'Johanna', 'F'
union
select 'Tyson', 'Fury', 'M'
go

Run Stored Procedure & Cache Result

 


set nocount on;
go

use [tempdb]
go

if object_id('tempdb..#Procedure1') is not null
begin
drop table #Procedure1
end

if object_id('tempdb..#Procedure2') is not null
begin
drop table #Procedure2
end

/*
Invoke Original SP and place in #Procedure1
*/
select *
into #Procedure1
FROM OPENQUERY([LOOPBACK], 'set fmtonly off; exec [tempdb].[unitest].[usp_friendAndfamily_list] @gender = '''' ')

/*
Invoke Revised SP and place in #Procedure2
*/
select *
into #Procedure2
FROM OPENQUERY([LOOPBACK], 'set fmtonly off; exec [tempdb].[unitest].[usp_friendAndfamily_list_v2] @gender = '''' ')

 

Result Set Metadata

Result Set Metadata – List

 


declare @tableName1 sysname
declare @tableName2 sysname
 
set @tableName1 = '#Procedure1%'
set @tableName2 = '#Procedure2%'
 
select
 
		  [sourced] = 'Temporary Tables Metadata'
		, [object] = tblSO.[name]
		, [columnID] = tblSC.[column_ID]
		, [columnName] = tblSC.[name]
		, [columnLength] = tblSC.[max_Length]
		, [columnType] = tblST.[name]
 
from tempdb.sys.objects tblSO
 
inner join tempdb.sys.columns tblSC
 
	on tblSO.object_id = tblSC.object_id
 
inner join tempdb.sys.types tblST
 
	on tblSC.[user_type_id] = tblST.[user_type_id]
 
where (
 
			   (tblSO.name like @tableName1+ '%')
			or (tblSO.name like @tableName2+ '%')
 
	  )
 
order by
 
		  tblSC.column_id asc
		, tblSO.name asc


Output:
metadataList

 

Result Set Metadata – Compare


DECLARE @tableName1 SYSNAME
DECLARE @tableName2 SYSNAME

SET @tableName1 = '#Procedure1%'
SET @tableName2 = '#Procedure2%';

WITH cte1
(
	  [objectName]
	, [columnID]
	, [columnName]
	, [columnLength]
	, [columnType]
)
AS
(
	SELECT
		  [objectName] = tblSO.[name]
		, [columnID] = tblSC.[column_id]
		, [columnName] = tblSC.[name]
		, [columnLength] = tblSC.max_length
		, [columnType] = tblST.[name]

	FROM tempdb.sys.objects tblSO

	INNER JOIN tempdb.sys.columns tblSC

		ON tblSO.object_id = tblSC.object_id

	INNER JOIN tempdb.sys.types tblST

		ON tblSC.[user_type_id] = tblST.[user_type_id]

	WHERE tblSO.NAME LIKE @tableName1 + '%'

)
,cte2
(
	  [objectName]
	, [columnID]
	, [columnName]
	, [columnLength]
	, [columnType]
)
AS
(
	SELECT
		  [objectName] = tblSO.[name]
		, [columnID] = tblSC.[column_id]
		, [columnName] = tblSC.[name]
		, [columnLength] = tblSC.max_length
		, [columnType] = tblST.[name]

	FROM tempdb.sys.objects tblSO

	INNER JOIN tempdb.sys.columns tblSC

		ON tblSO.object_id = tblSC.object_id

	INNER JOIN tempdb.sys.types tblST

		ON tblSC.[user_type_id] = tblST.[user_type_id]

	WHERE tblSO.NAME LIKE @tableName2 + '%'

)
SELECT

	  [sourced] = 'Temporary Table Differences'

	, [object] = tbl1.[objectName]

	, [columnID] = tbl1.[columnID]

	, [columnName] = tbl1.[columnName]

	, [columnType] = tbl1.[columnType]

	, [columnLength] = tbl1.[columnLength]

	, [status]

		= CASE

			--if missing
			WHEN tbl2.columnID IS NULL

				THEN 'Missing'

			WHEN
			(
				(tbl1.[columnType] != tbl2.[columnType])
				OR (tbl1.[columnLength] != tbl2.[columnLength])
			)
				THEN 'Different'

			ELSE 'Same'

	END

FROM cte1 tbl1

LEFT JOIN cte2 tbl2 

ON tbl1.[columnName] = tbl2.[columnName]

WHERE tbl1.[objectName] LIKE @tableName1 + '%'

AND   tbl2.[objectName] LIKE @tableName2 + '%'



Output:

compareMetadata

Compare Data

Test 1


use [tempdb]
go

declare @tableName1 sysname
declare @tableName2 sysname

if object_id('tempdb..#Procedure1') is not null
begin

	drop table #Procedure1

end

if object_id('tempdb..#Procedure2') is not null
begin

	drop table #Procedure2

end

set @tableName1 = '#Procedure1%'
set @tableName2 = '#Procedure2%'

select *
into #Procedure1
FROM OPENQUERY([LOOPBACK], 'set fmtonly off; exec [tempdb].[unitest].[usp_friendAndfamily_list] @gender = '''' ')

/*
Invoke Revised SP and place in #Procedure2
*/
select *
into #Procedure2
FROM OPENQUERY([LOOPBACK], 'set fmtonly off; exec [tempdb].[unitest].[usp_friendAndfamily_list_v2] @gender = '''' ')

;with cteP1
(
	  [PKcomputed]
	, [ChkSum]
)
as
(
	select

		  [PKcomputed] = [id]
		, [ChkSum] =
				CHECKSUM
				(
					  [lastname]
					, [firstname]
					, [gender]
				)
	from #Procedure1 tblP1
)
, cteP2
(
	  [PKcomputed]
	, [ChkSum]
)
as
(

	select

		  [PKcomputed] = [id]
		, [ChkSum] =
				CHECKSUM
				(
					  [lastname]
					, [firstname]
					, [gender]
				)

	from #Procedure2 tblP2
)
select

	[sourced]
		= 'Differences based on checksum (specified columns)'

	, [PKcomputed_2]
		= tblP2.[PKcomputed]

	, [Checkksum_2]
		= tblP2.[ChkSum]

	, [PKcomputed_1]
		= tblP1.[PKcomputed]

	, [Checkksum_1]
		=tblP1.[ChkSum]

	, [existInOriginalCall]
		=
			case
				when (tblP1.[PKcomputed] is null) then 'No'
				else 'Yes'
			end

from cteP2 tblP2

left outer join cteP1 tblP1

on tblP2.[PKcomputed] = tblP1.[PKcomputed]

WHERE
(

	(
		isNull(tblP2.[ChkSum], -1)
			!= isNull(tblP1.[ChkSum], -2)
	)

)
go

Output:

compareData.20151204

 

Explanation:

  1. The first entry with PK_2 is 0, is due to the error we built-in gender is 0 for original SP, and gender is 1 for revised SP
  2. The other two records are due to us returning all records for cases where invalid gender, @gender is space, is passed in
    • We did  not sanitize invalid gender in original SP
    • But, gracefully handled in our revised SP

Test 2

Here is another test that targets different table schema.

In this schema, the primary key is based on a set of columns.

Composite Primary Key


use [tempdb]
go
 
declare @tableName1 sysname
declare @tableName2 sysname
 
 
;with cteP1
(
	  [PKcomputed]
	, [csr]
	, [ChkSum]
)
as
(
	select
		  [PKcomputed] = [studentid]
							+ '_'
							+ convert(varchar(30), [created], 100)
							+ '_'
							+ convert(varchar(30), [apptime], 100)
		, [csr]
		, [ChkSum] =
					CHECKSUM
					(
						  [csr]
						, studentName
						, [hours]
						, [cancelled]
					)
	from #Procedure1 tblP1
)
, cteP2
(
	  [PKcomputed]
	, [csr]
	, [ChkSum]
)
as
(
 
	select
		  [PKcomputed] = [studentid]
							+ '_'
							+ convert(varchar(30), [created], 100)
							+ '_'
							+ convert(varchar(30), [apptime], 100)

		, [csr]
		, [ChkSum] =
			CHECKSUM
			(
	   			    [csr]
				  , [studentName]
				  , [hours]
				  , [cancelled]
			)
 
	from #Procedure2 tblP2
)
select
 
	[sourced]
		= 'Differences based on checksum (specified columns)'
 
	, [PKcomputed_2]
		=tblP2.[PKcomputed]

	, tblP2.[csr]
 
	, [Checkksum_2]
		=tblP2.[ChkSum]
 
	, [PKcomputed_1]
		=tblP1.[PKcomputed]
 
 	, tblP1.[csr]

	, [Checkksum_1]
		=tblP1.[ChkSum]
 
	, [existInOriginalCall]
		=
			case
				when (tblP1.[PKcomputed] is null) then 'No'
				else 'Yes'
			end
 
from cteP2 tblP2
 
left outer join cteP1 tblP1
 
on tblP2.[PKcomputed] = tblP1.[PKcomputed]
 
WHERE
(
 
	(
		isNull(tblP2.[ChkSum], -1)
			!= isNull(tblP1.[ChkSum], -2)
	)
 
)
go

 

 

 

Clean-up


/*
Database name 'tempdb' ignored, referencing object in tempdb.
*/
if object_id('#Procedure1') is not null
begin

drop table #Procedure1

end

if object_id('#Procedure2') is not null
begin

drop table #Procedure2

end

Error

There a are a few errors that one can encounter:

Server is not configured for Data Access

Error

Msg 7411, Level 16, State 1, Line 17
Server ‘LOOPBACK‘ is not configured for DATA ACCESS.

Resolution

  1. Make sure that linked Server’s data access is set to true
    • Example
      • EXEC master.dbo.sp_serveroption @server=N’LOOPBACK’, @optname=N’data access’, @optvalue=N’true’

 

Duplicate Column Names in Result Set

  1. Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name “[column-name]” is a duplicate.
    Msg 492, Level 16, State 1

    • Rename \ Remove duplicate column names in SP

 

References

There is quite a bit of good, solid work shared on Linked Server.

Here are some of it:

 

 

Summary

Wish I was successful in explaining  Derek Dieter’s work.  But, stuck.

It is not Hawaii 5 O, but it is 5 O’Clock somewhere.

And, it is Friday too.

 

Listening

George Harrison – Stuck Inside A Cloud

3 thoughts on “Transact SQL – Unit Test – Compare Stored Procedure ResultSet

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s