How to get a MAX value from 3 columns in SQL Server stored procedures?

user_17430585 0
Viewed 117

I'm working on a project with C# and a SQL Server database. Right now I have to write a stored procedure, which needs to get a MAX value from 3 different columns.

My code looks like this:

CREATE PROCEDURE [dbo].[sp_GetClass]
    @surname nvarchar(50),
    @maxClass int OUT
AS
    SELECT @maxClass = MAX 
    FROM (VALUES (Programming), (Mathematics), (Physics)) 
    FROM MissLess 
    WHERE Surname LIKE '%' + @surname + '%'
GO

I get an error for the second FROM and I'm not sure how to fix it.

The idea is to make it get the max value from those columns, when a surname is input. When no input is made(press enter) it just shows the max value from those 3 columns across all surnames.

Excuse me, if the formatting or the questions is not asked right, it's my first post here.

EDIT: made an error in @name, it's supposed to be @surname; and Name is supposed to be Surname. EDIT FINAL: Thanks for the awesome help. I'm an ultra newbie in all of this, but it all seems like great fun. Was so close to just giving up, but decided to give this a shot. Thanks for all the answers!!!

sql-server c#
3 Answers

You will need to put these three values for the chosen row(s) into a table variable inside your stored procedure, in order to be able to apply the MAX to them.

Try something like this - I didn't know what datatype those values are - just assuming INT here, adapt as needed:

CREATE PROCEDURE [dbo].[GetMaxValue]
    @surname NVARCHAR(50),
    @maxClass INT OUTPUT
AS
    DECLARE @IntTbl TABLE (AValue INT);
    
    INSERT INTO @IntTbl (AValue)
        SELECT Programming
        FROM MissLess 
        WHERE Name LIKE '%' + @name + '%'
        UNION ALL
        SELECT Mathematics
       FROM MissLess 
        WHERE Name LIKE '%' + @name + '%'
        UNION ALL
        SELECT Physics
        FROM MissLess 
        WHERE Name LIKE '%' + @name + '%';
        
    SELECT @maxClass = MAX(AValue)
    FROM @IntTbl;
GO

Update - ok, you asked for it ;-)

Since your search criteria might match multiple rows (at least you probably can't rule that out entirely), you'd have to first get the MAX() for each column, based on the search criteria:

CREATE PROCEDURE [dbo].[GetMaxValue]
    @surname NVARCHAR(50),
    @maxClass INT OUTPUT
AS
    DECLARE @MaxMath INT, @MaxPhys INT, @MaxProgr INT;
    
    SELECT 
        @MaxMath = MAX(Mathematics),
        @MaxPhys = MAX(Physics),
        @MaxProgr = MAX(Programming)
    FROM 
        MissLess 
    WHERE 
        Name LIKE '%' + @name + '%';

    IF @MaxMath > @MaxPhys
    BEGIN       
        IF @MaxMath > @MaxProgr
            -- @MaxMath is bigger than both @MaxPhys AND @MaxProgr --> it's the overall MAX()
            SET @maxClass = @MaxMath;
        ELSE
            -- @MaxMath is bigger than @MaxPhys, but smaller than @MaxProgr 
            SET @maxClass = @MaxProgr;
    END
    ELSE BEGIN
        IF @MaxPhys > @MaxProgr
            -- @MaxPhys is bigger than both @MaxMath AND @MaxProgr --> it's the overall MAX()
            SET @maxClass = @MaxPhys;
        ELSE
            -- @MaxPhys is bigger than @MaxMath, but smaller than @MaxProgr 
            SET @maxClass = @MaxProgr;
    END;

    SELECT @maxClass = MAX(AValue)
    FROM @IntTbl;
GO

Update #2: for a much nicer, cleaner solution for updating without using a table variable - see @AndrewCorrigan's answer further down, using a CASE expression.

user_133020

You can do (Fiddle)

SELECT MAX(s) 
FROM MissLess      
CROSS APPLY (VALUES (Programming), (Mathematics), (Physics)) V(s)
WHERE Name LIKE '%' + @name + '%'

If the @name predicate matches multiple rows you will have no indication of which row it came from though

user_732260

As an alternative solution using nested CASE statements:

SELECT @maxClass = MAX(CASE 
                             WHEN Programming >= Mathematics
                                  AND Programming >= Physics
                                      THEN Programming
                             WHEN Mathematics >= Physics
                                  AND Mathematics >= Programming
                                      THEN Mathematics
                             ELSE Physics
                       END)
FROM MissLess
WHERE Surname LIKE '%' + @surname + '%';
user_131689110
Related