Verifying Insertion of Records into  #Temp Table

mlcktmguy
mlcktmguy used Ask the Experts™
on
SQL Server Developer 14


I have a SP2 that is called from SP1.  

SP1 create a temp table then passes parameters to SP2.

SP2 Selects records from a view based on the passed parameters.

I created a unit test to test SP2 and verify it's results.

I set up a test with a bogus SP1 that calls SP2 with a controlled set of parameters.  When I execute the bogus SP! everything runs fine with no errors but I don't see the results that were inserted into the #temp table.

I thought maybe that was because there were no records matching my passed parameters so I removed the 'Where' clause form the select in SP2 and verified that here were records in the view being selected.  I still couldn't verify the records inserted into the #temp table by SP2.

How can I verify that the appropriate records were inserted into the #temp Table by SP2?

SP!
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spzBogus]    Script Date: 11/23/2017 8:40:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\MIke72\AppData\Local\Temp\~vs636E.sql


ALTER PROCEDURE [dbo].[spzBogus] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @passedMuniCode	             int,
			@passedExcludeHoldClass01			int,
			@passedExcludeHoldClass02			int,
			@passedExcludeHoldClass03			int,
			@passedExcludeHoldClass04			int

	Set @passedMuniCode	          = 877
	Set @passedExcludeHoldClass01 = 1		
	set	@passedExcludeHoldClass02 = null
	set	@passedExcludeHoldClass03 = null
	set	@passedExcludeHoldClass04 = null
	--


Create Table #tempExcludedTAs(TaxAuthorityID         int)

Execute spGetExcudedTAsBasedOnHoldRestriction @passedMuniCode,
													@passedExcludeHoldClass01,
													@passedExcludeHoldClass02,
													@passedExcludeHoldClass03,
													@passedExcludeHoldClass04


	
END

Open in new window


SP2
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetExcudedTAsBasedOnHoldRestriction]    Script Date: 11/23/2017 8:41:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetExcudedTAsBasedOnHoldRestriction] 
--
-- This will only be done on a set of records so no need to pass propertyID
-- Most holds will apply to the entire property rather than a specific TA so no TAID or Tax TypeID is passed
--
			@passedMuniCOde              int = null,

			@passedExcludeHoldClass01    int = null,
			@passedExcludeHoldClass02    int = null,
			@passedExcludeHoldClass03    int = null,
			@passedExcludeHoldClass04    int = null

  AS
  Begin

  SET NOCOUNT ON;

	insert Into #tempExcludedTAs
	SELECT      TaxAuthorityID
	From vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID

--	WHERE 
--		  ((@passedMuniCOde            is null) or (MuniCode      = @passedMuniCOde))  -- AND
--		  ((@passedExcludeHoldClass01  is null) or (HoldClassID IN (@passedExcludeHoldClass01,
--															    	@passedExcludeHoldClass02,
--																	@passedExcludeHoldClass03,
--																	@passedExcludeHoldClass04)));

 --
end 

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
I Just created the sample for you. It is working fine for me.

1. Created this SP.
CREATE PROCEDURE [dbo].[spGetExcudedTAsBasedOnHoldRestriction] 
(
	@passedMuniCOde              int = null,
	@passedExcludeHoldClass01    int = null,
	@passedExcludeHoldClass02    int = null,
	@passedExcludeHoldClass03    int = null,
	@passedExcludeHoldClass04    int = null
)
AS
Begin

  SET NOCOUNT ON;

	insert Into #tempExcludedTAs
	SELECT 1

	SELECT * FROM #tempExcludedTAs

end 

Open in new window


2. Created Second SP.

CREATE PROCEDURE [dbo].[spzBogus] 

AS
BEGIN
	
	SET NOCOUNT ON;

	declare @passedMuniCode	             int,
			@passedExcludeHoldClass01			int,
			@passedExcludeHoldClass02			int,
			@passedExcludeHoldClass03			int,
			@passedExcludeHoldClass04			int

	Set @passedMuniCode	          = 877
	Set @passedExcludeHoldClass01 = 1		
	set	@passedExcludeHoldClass02 = null
	set	@passedExcludeHoldClass03 = null
	set	@passedExcludeHoldClass04 = null	

	Create Table #tempExcludedTAs(TaxAuthorityID int)

	Execute spGetExcudedTAsBasedOnHoldRestriction @passedMuniCode,
													@passedExcludeHoldClass01,
													@passedExcludeHoldClass02,
													@passedExcludeHoldClass03,
													@passedExcludeHoldClass04


END

Open in new window


3. Execute the sp like below -

EXEC [spzBogus] 

Open in new window


OUTPUT

/*------------------------
EXEC [spzBogus] 
------------------------*/
TaxAuthorityID
--------------
1

Open in new window


Now your all code looks fine. Just a small change. You are not selecting data from the temp table.
Added below line - SELECT * FROM #tempExcludedTAs

Assuming below query returns data. Note if the below does not return any thing then you will not get anything. Just 0 rows with column Name.
--
SELECT      TaxAuthorityID
From vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID
--

Open in new window


Updated below procedure [dbo].[spGetExcudedTAsBasedOnHoldRestriction]

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetExcudedTAsBasedOnHoldRestriction]    Script Date: 11/23/2017 8:41:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetExcudedTAsBasedOnHoldRestriction] 
--
-- This will only be done on a set of records so no need to pass propertyID
-- Most holds will apply to the entire property rather than a specific TA so no TAID or Tax TypeID is passed
--
			@passedMuniCOde              int = null,

			@passedExcludeHoldClass01    int = null,
			@passedExcludeHoldClass02    int = null,
			@passedExcludeHoldClass03    int = null,
			@passedExcludeHoldClass04    int = null

  AS
  Begin

  SET NOCOUNT ON;

	insert Into #tempExcludedTAs
	SELECT      TaxAuthorityID
	From vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID

SELECT * FROM #tempExcludedTAs

--	WHERE 
--		  ((@passedMuniCOde            is null) or (MuniCode      = @passedMuniCOde))  -- AND
--		  ((@passedExcludeHoldClass01  is null) or (HoldClassID IN (@passedExcludeHoldClass01,
--															    	@passedExcludeHoldClass02,
--																	@passedExcludeHoldClass03,
--																	@passedExcludeHoldClass04)));

 --
end 

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Is it possible there are NULL values in what's being loaded?  That might cause an error since you didn't explicitly specify NULL or NOT NULL when creating the temp table.

Instead, do this in proc1, which will also make sure you get the best efficiency:
Create Table #tempExcludedTAs(TaxAuthorityID  int NOT NULL PRIMARY KEY)

And this in proc2:

      insert Into #tempExcludedTAs
      SELECT      DISTINCT TaxAuthorityID
      From vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID
      WHERE TaxAuthorityID IS NOT NULL
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I do not think NULL is an issue.

I just tested with a NULL and get the data. Also TaxAuthorityID does not look like containing NULLs.

/*------------------------
EXEC [spzBogus]
------------------------*/
TaxAuthorityID
--------------
NULL

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
When this CREATE is issued:

Create Table #tempExcludedTAs(TaxAuthorityID         int)

There are a large combination of factors that determine whether the column defaults to NULL or to NOT NULL.  Just because it comes out as allowing NULL on your instance does NOT mean that will necessarily be true on other instances.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Could you please inform me about the large combination of factors defaults to NULL or to NOT NULL.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Check Books Online, it's all described in there.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I already checked online books and MS site also but did not get anything. Could you please help.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Under "CREATE TABLE", "Nullability Rules Within a Table Definition".
Even after I read all this, I can't tell for sure what the default will be in any given db:
"
When you use CREATE TABLE or ALTER TABLE to create or alter a table, database and session settings influence and possibly override the nullability of the data type that is used in a column definition. We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns ...

When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. ...

System-supplied data type
If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.
When any session settings are set ON by using SET:
  ANSI_NULL_DFLT_ON = ON, NULL is assigned.
  ANSI_NULL_DFLT_OFF = ON, NOT NULL is assigned.
When any database settings are configured by using ALTER DATABASE:
  ANSI_NULL_DEFAULT_ON = ON, NULL is assigned.
  ANSI_NULL_DEFAULT_OFF = ON, NOT NULL is assigned.
To view the database setting for ANSI_NULL_DEFAULT, use the sys.databases catalog view.

When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI_NULL_DEFAULTis OFF), the default of NOT NULL is assigned.
"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial