Stored Proc Question

17 Sep 2005 4:35 PM

Joe Delphi

Hi,

I have a stored procedure, CLEAN_ACQ_REPORTS which calls another stored
procedure, SELECT_TPRD, both of which are shown below. The SELECT_TPRD proc
runs fine by itself and returns the correct string value. But when I nest
it inside of CLEAN_ACQ_REPORTS, it returns a NULL value. Why is this? What
am I doing wrong?

JD

CREATE PROCEDURE [dbo].[CLEAN_ACQ_REPORTS] AS
DECLARE
@TPRD VARCHAR(6)
EXEC SELECT_TPRD @TPRD

DELETE
From tblAcqReports
Where Tprd = @TPRD

----------------------------------------------------------------------------
---
CREATE PROCEDURE [SELECT_TPRD]
@TPRD VARCHAR(6) OUTPUT
AS

SELECT @TPRD = TPRD
FROM tblCurrentMonth

RETURN

17 Sep 2005 4:40 PM

John Bell

Hi

Try calling SELECT_TPRD specifying that @TPRD is an output parameter.

EXEC SELECT_TPRD @TPRD OUTPUT

John

"Joe Delphi" <delphi561@nospam.cox.net> wrote in message

news:6BXWe.253549$E95.75472@fed1read01...
Show quote

> Hi,
>
> I have a stored procedure, CLEAN_ACQ_REPORTS which calls another stored
> procedure, SELECT_TPRD, both of which are shown below. The SELECT_TPRD
> proc
> runs fine by itself and returns the correct string value. But when I nest
> it inside of CLEAN_ACQ_REPORTS, it returns a NULL value. Why is this?
> What
> am I doing wrong?
>
> JD
>
>
>
> CREATE PROCEDURE [dbo].[CLEAN_ACQ_REPORTS] AS
> DECLARE
> @TPRD VARCHAR(6)
> EXEC SELECT_TPRD @TPRD
>
> DELETE
> From tblAcqReports
> Where Tprd = @TPRD
>
> ----------------------------------------------------------------------------
> ---
> CREATE PROCEDURE [SELECT_TPRD]
> @TPRD VARCHAR(6) OUTPUT
> AS
>
> SELECT @TPRD = TPRD
> FROM tblCurrentMonth
>
> RETURN
>
>

17 Sep 2005 4:44 PM

David Portas

You have to declare a parameter as OUTPUT both in the declaration and when
you call the proc. You missed OUTPUT from the EXEC:

EXEC SELECT_TPRD @TPRD OUTPUT

--
David Portas
SQL Server MVP
--