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

Pawan Kumar

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.

Pawan Kumar

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mlcktmguy

ASKER

Excellent, thank you.