Solved

Function to list Max value of a field in a table

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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