You must Sign In to post a response.
  • Category: SQL Server

    Retrieve max value of any column using function

    hi, in my work i always need max(ID) of every table.so i want to create a function to do it.
    i have written following function.but it does not work.please help me..

    create function fn_MaxID(@tbl varchar(20),@col varchar(20))
    returns int
    as
    begin
    declare @MaxID int set @MaxID = 0
    DECLARE @sqlCommand varchar(1000)
    SET @sqlCommand = 'SELECT '+ max(isnull(@col,0)) + ' from '+ @tbl +''
    exec(@sqlCommand)
    select @MaxID = exec(@sqlCommand)------Error Line-------
    return
    end
  • #765088
    Hi Rashed,

    Whether it throws any error, if yes please share the error details so that we can help you better.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #765095
    Can you try the "SET" command.
    Can you try the following

    create function fn_MaxID(@tbl varchar(20),@col varchar(20))
    returns int
    as
    begin
    declare @MaxID int set @MaxID = 0
    DECLARE @sqlCommand varchar(1000)
    SET @sqlCommand = 'SELECT '+ max(isnull(@col,0)) + ' from '+ @tbl +''
    exec(@sqlCommand)
    SET @MaxID = exec(@sqlCommand)------Error Line-------
    return
    end

    By Nathan
    Direction is important than speed

  • #765103
    You can't call dynamic query inside the function,instead try to create a SP with output parameter

  • #765118
    Hi

    You can follow this Query



    create proc sp_getMaxID(@tbl varchar(20),@col varchar(20))
    as
    begin
    declare @MaxID int set @MaxID = 0
    DECLARE @sqlCommand varchar(1000)
    SET @sqlCommand = 'SELECT '+ max(isnull(@col,0)) + ' from '+ @tbl +''
    exec(@sqlCommand)
    end

    exec sp_getMaxID 'PayNamelist','ID'


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.


Sign In to post your comments