Avatar of mlcktmguy
mlcktmguy
Flag for United States of America

asked on 

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

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

Open in new window

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*/

Open in new window


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

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
mlcktmguy

8/22/2022 - Mon