Microsoft SQL Server
Microsoft SQL Server

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: 2017-11-23

Who is Participating?

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-23

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

LVL 71

Scott PletcherSenior DBACommented: 2017-11-24

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

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-24

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

LVL 71

Scott PletcherSenior DBACommented: 2017-11-27

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.

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-27

Could you please inform me about the large combination of factors defaults to NULL or to NOT NULL.

LVL 71

Scott PletcherSenior DBACommented: 2017-11-27

Check Books Online, it's all described in there.

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-27

I already checked online books and MS site also but did not get anything. Could you please help.

LVL 71

Scott PletcherSenior DBACommented: 2017-11-27

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