CODEWITHSUNDEEP
sqlsql-serverstored-proceduresmax
Lara
Lara

Reputation: 33

Built-in MAX function only returns an integer

I am trying to write a stored procedure ( using SQL Server Management Studio 2008 R2) to retrieve the maximum measurement value from a table. This seemed like an easy thing to do, so I wrote a short stored procedure to get the MAX. However, when I ran the procedure, it returned a 0 rather than the expected 0.018 value.

When I hover my mouse over the MAX function, it displays "built-in function MAX(expression) RETURNS int". So it seems to be casting my result as an int. This is rather annoying.

I guess I'll have to calculate the maximum the long way unless anyone here has an easier solution. Any ideas?

ALTER PROCEDURE [dbo].[GetMaxOscillation] (@SerialNumber varchar (16), @Date datetime)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MaxOsc DECIMAL(18,6)

    SELECT @MaxOsc = MAX(Value)
    FROM LoadListingOscillations
    WHERE SerialNumber=@SerialNumber AND Date=@Date AND Value IS NOT NULL


    RETURN @MaxOsc

    SET NOCOUNT OFF
END

Upvotes: 3

Views: 4530

Answers (2)

KM.
KM.

Reputation: 103607

stored procedures communicate in three ways: the RETURN value, and OUTPUT parameter and a result set

RETURN VALUE
if you need to return an integer, you can use this method:

CREATE PROCEDURE GetMyInt
( @Param int)
AS
DECLARE @ReturnValue int

SELECT @ReturnValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN @ReturnValue
GO

and now call your procedure like:

DECLARE @SelectedValue int
       ,@Param         int
SET @Param=1
EXEC @SelectedValue = GetMyInt @Param
PRINT @SelectedValue

this will only work for INTs, because RETURN can only return a single int value and nulls are converted to a zero.

OUTPUT PARAMETER
you can use an output parameter:

CREATE PROCEDURE GetMyInt
( @Param     int
 ,@OutValue  int OUTPUT)
AS
SELECT @OutValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

and now call your procedure like:

DECLARE @SelectedValue int
       ,@Param         int
SET @Param=1
EXEC GetMyInt @Param, @SelectedValue OUTPUT
PRINT @SelectedValue

Output parameters can only return one value, but can be any data type

RESULT SET for a result set make the procedure like:

CREATE PROCEDURE GetMyInt
( @Param     int)
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

use it like:

DECLARE @ResultSet table (SelectedValue int)
DECLARE @Param int
SET @Param=1
INSERT INTO @ResultSet (SelectedValue)
    EXEC GetMyInt @Param
SELECT * FROM @ResultSet

result sets can have many rows and many columns of any data type

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67085

You have to do MAX(CAST(Value AS DECIMAL(18,6)))

I am guessing that the Value is not a DECIMAL, so you must cast it before the assignment

Here is the SQLFiddle to prove that if the table is set up as a Decimal then this will work

Here is a SQLFiddle that shows that the cast is needed

UPDATE

If Value is indeed a Decimal(18,6), then you need to make sure of your table values. Because, as you can see from my examples, this should work

The last thing that is probably more your problem is that you are returning an integer. I do not think you can return anything but an integer in SQL Server. You will have to do a SELECT @MaxOsc or use an OUTPUT variable

Upvotes: 4

Related Questions