asked on

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Pawan Kumar

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

Scott Pletcher

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 Kumar

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

Scott Pletcher

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

Pawan Kumar

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

Scott Pletcher

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