Link to home
Create AccountLog in
Avatar of donhannam
donhannamFlag for New Zealand

asked on

Function to list Max value of a field in a table

I have a query to list fields in a database with a particular string in them:-

SELECT     sys.tables.name AS TableName, sys.columns.name AS FieldName
FROM         sys.columns INNER JOIN
                      sys.tables ON sys.columns.object_id = sys.tables.object_id
WHERE     (sys.columns.name LIKE '%Job%')
ORDER BY TableName

I use this quite a bit and in this case would like to get an idea of the data in each field so wanted to add a column to show the max value of each field so I created a function:-

CREATE FUNCTION[dbo].[FN_MaxValue](@TableName Varchar(50), @Field VarChar(50))
RETURNS Varchar(50)
AS
BEGIN
Declare @MaxVal varchar(50)
Declare @strSQL as varchar(100)
set @strSQL = 'SELECT @MaxVal = Max(' + @Field + ') FROM ' + @tablename
Exec @strSQL

RETURN @MaxVal
End


This does not work - The issue I have is passing a table name to the sql statement. I have tried a couple of options but no luck. Appreciate any ideas on getting this to work.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of donhannam

ASKER

OK thanks - I had a look at doing a stored procedure but not sure how to return values - I decided easier to write a separate program.