Verifying Insertion of Records into #Temp Table

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

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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 ExpertCommented:
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

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Enroll Today!
Scott PletcherSenior DBACommented:
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 ExpertCommented:
Could you please inform me about the large combination of factors defaults to NULL or to NOT NULL.
Scott PletcherSenior DBACommented:
Check Books Online, it's all described in there.
Pawan KumarDatabase ExpertCommented:
I already checked online books and MS site also but did not get anything. Could you please help.
Scott PletcherSenior DBACommented:
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.
"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.