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

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
SOLUTION 1 - USING TEMP TABLE

I have created sample procedure ( PROC1, and PROC 2)  and shown how we can get value from one procedure to another. Detailed Explanation is written inside the proc2.

CREATE PROC1

CREATE PROC Proc1
(
	@ID INT OUTPUT
)
AS
BEGIN

	SELECT @ID = 20 

	SELECT @ID

	RETURN
END
GO

Open in new window


CREATE PROC2

CREATE PROC Proc2
(
	@Vals INT 
)
AS
BEGIN

		IF OBJECT_ID('tempdb..#tempDATA') IS NOT NULL DROP TABLE #tempDATA /*Drop the temp table if already there else create the new temp table*/
		CREATE TABLE #tempDATA ( ID INT )

		INSERT INTO #tempDATA /*Just above executing the SP use the insert into command, this will execute the sp and we will get the data in the temp table*/
		EXEC Proc1 @Vals                     /*Passing Value to the PROC 1*/

		DECLARE @RecieveVALUE AS INT
		SELECT TOP 1 @RecieveVALUE = ID FROM #tempDATA /* GET the value from the temp table to the variable*/

		SELECT @RecieveVALUE ReturnValuefromPROC1 /*Then we just need to select the data*/
END

Open in new window


EXECUTION

EXEC Proc2 0 

Open in new window


OUTPUT

/*------------------------
OUTPUT 
------------------------*/

ReturnValuefromPROC1
--------------------
20

(1 row(s) affected)

Open in new window


Will provide you another solution using output parameters in sometime.
0
Pawan KumarDatabase ExpertCommented:
SOLUTION 2 - USING OUTPUT Parameters

I have created sample procedure ( PROC1, and PROC 2)  and shown how we can get value from one procedure to another using OUTPUT paramters. Detailed Explanation is written inside the proc2.

CREATE PROC1

CREATE PROC Proc1
(
	@ID INT OUTPUT
)
AS
BEGIN

	SELECT @ID = 20 	

	RETURN
END
GO

Open in new window


CREATE PROC2

CREATE PROC Proc2
(
	@Vals INT 
)
AS
BEGIN

		DECLARE @RecieveVALUE AS INT 		

		EXECUTE [dbo].Proc1 @RecieveVALUE OUTPUT /* This is how we call the other SP and GET THE value in the local variable from other SP */

		SELECT @RecieveVALUE ReturnValuefromPROC1  
END

Open in new window


EXECUTION

EXEC Proc2 0 

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
ReturnValuefromPROC1
--------------------
20

(1 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Updated your code ... Please try and let us know in case any issues. Changed I have marked in bold for your reference.

USE [JTSConversion]
GO
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=0

  Execute dbo.spCheckIfHoldAlreadyExistsOnThisTA @passedPropertyID, @passedTaxAuthorityID, @passedHoldTypeID, @ReturnHoldRecID OUTPUT
 
 If @ReturnHoldRecID <= 0
  BEGIN
       Insert INTO dbo.tblHolds(TaxAuthorityID,PropertyID,HoldTypeID,HoldEffectiveDate,Comment,DateAdded,UserAdded)
       VALUES (@passedTaxAuthorityID,@passedPropertyID,@passedHoldTypeID,@passedHoldEffectiveDate,@passedComment,@passedDateAdded,@passedUserAdded)
  End

END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Excellent, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.