Post reply

to find the maximum of a integer column of a table using a function

  • June 26, 2005 at 4:58 am

    #88756

    i want to create a function to find the maximum of a integer column by sending the table name and the column name as the parameters for the function

  • June 26, 2005 at 5:50 am

    #568466

    Here's script that does just that... and more :

    http://www.sqlservercentral.com/scripts/contributions/1468.asp

  • June 27, 2005 at 2:59 am

    #568516

    sorry mr.remi gregoire.. i can't catch ur point clearly.. can u explain it in a detailed manner.. or can u give the script of the function(which returns the max of the column passed as parameter to the function

  • June 27, 2005 at 7:03 am

    #568558

    To find the result you are looking for requires the use of dynamic sql which cannot be used in a function, only in a procedure like this

    CREATE PROCEDURE usp_GetMaxValue

        (@TableName varchar(50), @ColumnName varchar(50), @MaxValue int OUTPUT)

    AS

        DECLARE @sql nvarchar(150)

        SET @sql = 'SELECT @MaxValue = MAX([' + @ColumnName + ']) FROM [' + @TableName + ']'

        EXECUTE sp_executesql @sql,N'@MaxValue int OUTPUT',@MaxValue OUTPUT

    GO

    DECLARE @max-2 int

    EXEC usp_GetMaxValue 'Orders', 'OrderID', @max-2 output

    SELECT @max-2

    The only way to do this in a function is to test for each combination of table and/or column

    DECLARE @MaxValue int

    IF @TableName = 'Orders'

        SELECT @MaxValue = MAX(OrderID) FROM Orders

    IF @TableName = 'Products'

        SELECT @MaxValue = MAX(ProductID) FROM Products

    RETURN @MaxValue

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 27, 2005 at 8:06 am

    #568611

    As he says .

  • June 28, 2005 at 3:01 am

    #568851

    thanq gurus..

  • June 28, 2005 at 9:59 am

    #569011

    HTH.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply