December 2, 2003 at 10:06 am
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?
December 2, 2003 at 10:40 am
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.............
December 3, 2003 at 1:41 pm
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
December 4, 2003 at 2:42 am
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.
December 4, 2003 at 2:12 pm
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