troubleshooting Question

Test a SPROC, Executing Line By line and Viewing Variable Contents

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft SQL Server
4 Comments1 Solution92 ViewsLast Modified:
I'm not getting the expected results from my SPROC.  I am testing by right clicking on the SPROC in SSMA and selecting 'Execute Stored Procedure'
I then enter the parameters and press the 'OK' button.

The SPROC runs to completion but the result isn't what is expected.

Is there any way to test the SPROC step by step?  In the SPROC below I would like to know the value of @ReturnHoldRecID after the Execute statement
  Execute dbo.spCheckIfHoldAlreadyExistsOnThisTA @passedPropertyID, @passedTaxAuthorityID, @passedHoldTypeID, @ReturnHoldRecID
It should be returning a value greater than zero, which should then exit the SPROC without going any further due to the IF Statement
 If @ReturnHoldRecID > 0
	  Begin
		--
      Return 0
  End /*If*/

But it continues thru and writes the record.

Here is the SPROC
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[sptblHold_Insert]    Script Date: 11/28/2017 10:53:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptblHold_Insert] 
	@passedTaxAuthorityID		int          = 0,
	@passedPropertyID			int          = 0,
	@passedHoldTypeID			int          = 0,	
	@passedHoldEffectiveDate	datetime     = null,
	@passedComment			    nvarchar(Max)= null, 
	@passedDateAdded			datetime     = null,
	@passedUserAdded			nvarchar(50) = null
As

Begin

Set NOCOUNT on;

	Declare @ReturnHoldRecID   int

  Execute dbo.spCheckIfHoldAlreadyExistsOnThisTA @passedPropertyID, @passedTaxAuthorityID, @passedHoldTypeID, @ReturnHoldRecID
  --
  -- @ReturnRecID is the record number where the TA and HOld were found
  -- If @ReturnRecID is greater than zero the hold is already on the record and we can get out.
  --
  If @ReturnHoldRecID > 0
	  Begin
		--
      Return 0
  End /*If*/

--
-- The Hold is not already on this TA or Property so Add it.
--
Insert dbo.tblHolds (
			TaxAuthorityID, 
			PropertyID,
			HoldTypeID,
			HoldEffectiveDate,
			Comment,
			DateAdded, 
			UserAdded)

	Values ( 
			@passedTaxAuthorityID    , 
			@passedPropertyID	     ,
			@passedHoldTypeID	     ,
			@passedHoldEffectiveDate ,	
			@passedComment		     ,
			@passedDateAdded	     , 
			@passedUserAdded	     )

	--Set @NewPayYearID = SCOPE_IDENTITY()
--	RETURN @NewPayCostID

END
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros