Solved

Function to list Max value of a field in a table

Posted on 2013-05-14
2
295 Views
Last Modified: 2013-05-14
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.
0
Comment
Question by:donhannam
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 39166598
Unfortunately dynamic sql's cant be executed directly within a SQL Server function, you should either write a CLR or a stored procedure to do this/
0
 

Author Closing Comment

by:donhannam
ID: 39166830
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.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now