RETURN VALUE - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

RETURN VALUE

Author

Topic

sjesweak
Starting Member

10 Posts

Posted - 2007-08-28 : 10:40:43

Hello all -

I'm trying the following:

ALTER PROCEDURE dbo.returnWeekPrice
( @WeekDate DATETIME,
@PART_NUM VARCHAR(30),
@PO_NUM VARCHAR(30),
@PRICE FLOAT OUTPUT )
AS
SET @PRICE =(SELECT TOP 1 PRICE FROM tbl_RPS_PART_PO_PRICES WHERE PART_NUM = @PART_NUM AND PO_NUM = @PO_NUM AND DATE_EFF < @WeekDate ORDER BY DATE_EFF DESC)

RETURN @PRICE

When I run this I get the following:

Running [dbo].[returnWeekPrice] ( @WeekDate = 8/13/2007, @PART_NUM = 96-001900, @PO_NUM = 002942, @PRICE = 0 ).

No rows affected.
(0 row(s) returned)
@PRICE = 3.6
@RETURN_VALUE = 3
Finished running [dbo].[returnWeekPrice].

WHY is the return value 3 and not 3.6????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 10:54:06

How have you declared the @PRICE parameter outside the stored procedure?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 10:56:54

[code]ALTER PROCEDURE dbo.returnWeekPrice
(
@WeekDate DATETIME,
@PART_NUM VARCHAR(30),
@PO_NUM VARCHAR(30),
@PRICE FLOAT OUTPUT
)
AS

SELECT TOP 1 @PRICE = PRICE
FROM tbl_RPS_PART_PO_PRICES
WHERE PART_NUM = @PART_NUM
AND PO_NUM = @PO_NUM
AND DATE_EFF < @WeekDate
ORDER BY DATE_EFF DESC[/code]

DECLARE @Price FLOAT

EXEC dbo.returnWeekPrice '20070808', 'Part1', 'PONUM', @Price OUT

SELECT @Price

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 10:57:29

A tip free of charge. This code should be made as a function instead for versatility.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 10:58:39

SELECT dbo.fn('20070808', 'Part1', 'PONUM')

CREATE FUNCTION	dbo.returnWeekPrice
(
	@WeekDate DATETIME,
	@PART_NUM VARCHAR(30),
	@PO_NUM VARCHAR(30)
)
RETURNS FLOAT
AS
BEGIN
	RETURN	(
			SELECT TOP 1	PRICE
			FROM		tbl_RPS_PART_PO_PRICES
			WHERE		PART_NUM = @PART_NUM
					AND PO_NUM = @PO_NUM
					AND DATE_EFF < @WeekDate
			ORDER BY	DATE_EFF DESC
		)
END

E 12°55'05.25"
N 56°04'39.16"

sjesweak
Starting Member

10 Posts

Posted - 2007-08-28 : 11:00:21

One of my friends was telling me in email that the sql return only returns integers. Not sure of why that would be.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 11:02:26

Get new friends.

E 12°55'05.25"
N 56°04'39.16"

sjesweak
Starting Member

10 Posts

Posted - 2007-08-28 : 11:10:59

Got it all I had to change was the return to a select @price
and then in my calling exec statement make sure @Price OUT was in the parameter field.

Thx guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 11:15:36

As I posted at 08/28/2007 : 10:56:54 ?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources