We help IT Professionals succeed at work.
Get Started

Verifying Insertion of Records into  #Temp Table

mlcktmguy
mlcktmguy asked
on
98 Views
Last Modified: 2017-11-27
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
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This problem has been solved!
Unlock 2 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE