Passing Variables between StocProc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing Variables between StocProc

Author

Topic

JHCOLVI
Starting Member

13 Posts

Posted - 2005-06-27 : 18:37:32

I want to pass a variable into a stored procedure and output another variable back to the store procedure I called it from. I am get an error saying that I need to declare the output variable.
What am I doing wrong? Here is the code:
1st Procedure:
CREATE PROCEDURE spCallingVariable
DECLARE @DP VARCHAR(100)
DECLARE @SGT VARCHAR(100)
DECLARE @SGT1 VARCHAR(100)
DECLARE @TRGTABLE VARCHAR (100)

SET @DP='2015801'
SET @TRGTABLE='R200503'

Exec spSGT @DP, @TRGTABLE, @SGT1 = @SGT OUTPUT

PRINT @SGT
GO

2nd Procedure:
CREATE PROCEDURE spSGT (@DP VARCHAR(100), DECLARE @TRGTABLE VARCHAR (100), @SGT1 varchar(100) OUTPUT)
AS
DECLARE @SQL99 nvarchar(100)
SET @SQL99 ='@SGT1=(SELECT MAX(SGT)FROM ' + @TRGTABLE + '_TARGETS WHERE (Region_Item_ID =' + @DP + '))'
SELECT @SQL99
Exec (@SQL99)
GO

@DP and @TRGTABLE are the variables that I am passing into the 2nd Procedure. And @SGT1 is the output variable that I want to pass back to the 1st procedure. @SGT is the result that I want from the 1st procedure.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-27 : 21:11:05

-- You didn't get that error with the code you posted. It won't even compile. You should test stuff before you post it here so you don't look like an idiot.

--
-- See if this works for you:

--


-- 1st Procedure:

CREATE PROCEDURE spCallingVariable 
AS
DECLARE @DP VARCHAR(100)
DECLARE @SGT VARCHAR(100)
DECLARE @SGT1 VARCHAR(100)
DECLARE @TRGTABLE VARCHAR (100)

SET @DP='2015801'
SET @TRGTABLE='R200503'

Exec spSGT @DP, @SGT1 = @SGT OUTPUT

PRINT @SGT 
GO

-- 2nd Procedure:
CREATE PROCEDURE spSGT
	@DP VARCHAR(100), 
	@SGT1 varchar(100) OUTPUT

AS

SELECT @SGT1 = 'whatif'
SELECT @SGT1

GO

EXEC spCallingVariable
GO

DROP PROCEDURE spCallingVariable
GO
DROP PROCEDURE spSGT
GO
--

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources