Function with variable table and column name

  • How can I create function that has two input parameters: table name and column name and return the biggest value in that column of that table?

  • I don't think your going to be able to easily do this in a function. It can be done quite easily in an SP:

    create proc Max

    @T varchar(255),

    @C varchar(255)

    as

    declare @SQL varchar(500)

    set @SQL = 'select max(' + @C + ') from ' + @T

    exec (@SQL)

    However in a function you cannot use exec to run a built query, and you cannot easily query a table with a variable table name without using dynamic sql.

    Without knowing and listing all table and column names combinations in some hideous case or if statement, I think you're out of luck.............

  • Technically, you can do this, although it's not optimal, as the Query is not compiled.

    you can run the following on pubs...

    use pubs

    /*----------------------------------------------------

    create function that returns query text

    */----------------------------------------------------

    if object_ID('GetMax') is not null drop function GetMax

    go

    create function GetMax (@TableName sysname, @ColumnName sysname)

    returns varchar(8000)

    as

    begin

    return 'Select max(' + @ColumnName + ') as ''' + @ColumnName + ''' From ' + @TableName + ' (nolock)'

    end

    ---test function

    select dbo.GetMax('authors', 'au_id')

    ---Call function

    declare @Query varchar(8000)

    select @Query = dbo.GetMax('authors', 'au_id')

    exec (@Query)

    Signature is NULL

  • Thank you for your answers. The idea was to create function that will simulate AutoNumber on a varchar column and will be used in INSERT statements. Other solution is using triggers, but we don't want this.

  • That is not the way you want to do it, then, as it requires an execute...all dynamic code must have an Exec. I think you're on the wrong track here.

    Here's something that you could possibly use, although you'd want to test thoroughly. this proc ties into the object and index system tables, so if you're indexes are not being maintained properly you'll get funky results:

    use pubs

    create function RowsinTable

    (@TableName sysname)

    returns int

    as

    begin

    declare @RowsinTable int

    Select @RowsinTable = sc.rowcnt

    Fromsysobjects so (nolock)

    JOINsysindexes sc (nolock) on so.id = sc.id

    WHERE sc.indid < 2 and so.Name = @TableName

    return @RowsinTable

    END

    ---to select

    select dbo.RowsinTable('authors')

    Signature is NULL

Viewing 5 posts - 1 through 4 (of 4 total)

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