How to change data type of output of stored procedure? I want to output money data type but procedure always returns int

Question

This is my procedure:

ALTER PROCEDURE spMaxOfInvoiceTotal
AS
BEGIN
    DECLARE @max MONEY
    SET @max = (SELECT MAX(InvoiceTotal) FROM Invoices)

    PRINT @MAX
    RETURN @MAX
END
GO

But when I execute, it returns int not money type.

DECLARE @return_value int

EXEC    @return_value = [dbo].[spMaxOfInvoiceTotal]

SELECT  'Return Value' = @return_value
GO

As a result, a value is incorrect. It has to be 37966.19. But procedure returns 37966.

Even if I change @return_value money, I still get int. How to change procedure so return value would be money?

score 0 · Answer 1 · answered Oct 02 '14 at 00:14

0

Stored procedure return value is used to return exit code, it is integer.

You should define output parameter

CREATE PROCEDURE mysp @Maxval MONEY OUTPUT

http://msdn.microsoft.com/en-us/library/ms188655.aspx

answered Oct 02 '14 at 00:14

radar

13,270
2
25
33

Now procedure outputs two results. @max 37966.16 return 37966 How to get only one result (@max 37966.16)? – Timur Oct 02 '14 at 00:24
@Timur, you can just do return and select out variable alone, check out http://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter – radar Oct 02 '14 at 00:45
@Timur, were you able to fix it? – radar Oct 02 '14 at 14:20

score 0 · Answer 2 · answered Oct 02 '14 at 04:56

What RDBMS is this for? SQL Server?

The value returned from a stored procedure in SQL Server is always INT and you can't change that - it's typically used to convey back a success/failure flag, or a "number of rows affected" information.

If you to "return" a MONEY (or better yet: DECIMAL) value - you can either use an OUTPUT parameter (which works fine for a single value), or you need to return a result set with that value.

So in your case, you could try something like:

CREATE PROCEDURE GetMaxOfInvoiceTotal
    @MaxValue DECIMAL(20,4) OUTPUT
AS
BEGIN
    SET @MaxValue = (SELECT MAX(InvoiceTotal) FROM Invoices)
END
GO

and then call this stored procedure like this:

DECLARE @RC INT
DECLARE @MaxValue DECIMAL(20,4)

EXECUTE @RC = [dbo].[GetMaxOfInvoiceTotal] @MaxValue OUTPUT
GO

How to change data type of output of stored procedure? I want to output money data type but procedure always returns int

2 Answers2