Solved

Function to list Max value of a field in a table

Posted on 2013-05-14
2
297 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

22 Experts available now in Live!

Get 1:1 Help Now