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.
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!
SP2
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I Just created the sample for you. It is working fine for me.
1. Created this SP.
2. Created Second SP.
3. Execute the sp like below -
OUTPUT
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.
Updated below procedure [dbo].[spGetExcudedTAsBase
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
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
3. Execute the sp like below -
EXEC [spzBogus]
OUTPUT
/*------------------------
EXEC [spzBogus]
------------------------*/
TaxAuthorityID
--------------
1
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
--
Updated below procedure [dbo].[spGetExcudedTAsBase
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
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(TaxAuthor
And this in proc2:
insert Into #tempExcludedTAs
SELECT DISTINCT TaxAuthorityID
From vtblTaxAuthortity_PropID_T
WHERE TaxAuthorityID IS NOT NULL
Instead, do this in proc1, which will also make sure you get the best efficiency:
Create Table #tempExcludedTAs(TaxAuthor
And this in proc2:
insert Into #tempExcludedTAs
SELECT DISTINCT TaxAuthorityID
From vtblTaxAuthortity_PropID_T
WHERE TaxAuthorityID IS NOT NULL
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.
I just tested with a NULL and get the data. Also TaxAuthorityID does not look like containing NULLs.
/*------------------------
EXEC [spzBogus]
------------------------*/
TaxAuthorityID
--------------
NULL
When this CREATE is issued:
Create Table #tempExcludedTAs(TaxAuthor
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.
Create Table #tempExcludedTAs(TaxAuthor
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.
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.
"
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.
"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial