How to send tableName and columnName to sql function and return result

Question

I want to create a T-SQL function, send table name and column name to it, and get the max value of this column.

I wrote a function like this:

CREATE FUNCTION getMaxValue 
     (@TableName nvarchar(30),@FieldName nvarchar(30))
RETURNS nvarchar(max) 
AS  
BEGIN 
    DECLARE @SqlString nvarchar(MAX)
    --declare @Result nvarchar(MAX)

    SET @SqlString = ' select max( ' + @FieldName + ') from ' + @TableName 
    RETURN EXEC(@SqlString)
END

but I can't use EXEC in SET, SELECT or RETURN within this function.

Please help me to solve this problem.

because I want use it in a select command as: `select TABLE_NAME ,COLUMN_NAME, getMaxValue (TABLE_NAME,COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS` — Mohammad Ahmadi, Jan 25 '16 at 20:30
Why in the world do you want to do this? It is super simple to get the max value from a column in a table. How is this any better than just executing the query? — Sean Lange, Jan 25 '16 at 20:33
Oh...you are wanting to get the max value from every column in a given table? — Sean Lange, Jan 25 '16 at 20:34
You can't call EXEC from sql function. Use stored procedure instead — niketshah90, Jan 25 '16 at 20:35
I want to have a result like this: `tableName, ColumnName, MaxValue`. I want this result for all tables and columns in my database. — Mohammad Ahmadi, Jan 25 '16 at 20:47
How are you going to handle datatypes that are invalid for MAX? Like bit? Guid? etc... — Sean Lange, Jan 25 '16 at 20:48
Also, how many columns are in your database? This could seriously take hours to run if you have a larger database because you will have to run a query for every single column in every single table. — Sean Lange, Jan 25 '16 at 20:50
Seriously? Every single column in every single table is a number? — Sean Lange, Jan 25 '16 at 20:51
My database is not very large, about 20 table and in average 10 column in each table. And maximum record count in my tables is less than 1000. so I dont think it take many time. — Mohammad Ahmadi, Jan 25 '16 at 20:54

score 5 · Accepted Answer · answered Jan 25 '16 at 20:54

This is a very strange request and will not work in nearly every database I have ever encountered. It also would not make sense in any system I have ever worked with but you can leverage dynamic sql for such a thing. I hope you are patient because this is going to unbelievably slow. But you are asking sql to get you the max value of every single column of every single table. There is no way to do this quickly.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'select ''' + TABLE_NAME + ''' as TableName, ''' + COLUMN_NAME + ''' as ColumnName, cast(Max([' + COLUMN_NAME + ']) as varchar(max)) as MaxValue from [' + TABLE_NAME + '] union all '
from INFORMATION_SCHEMA.COLUMNS

set @SQL = LEFT(@SQL, len(@SQL) - 10)

select @SQL
--uncomment below when you are comfortable with this.
--exec sp_executesql @SQL

Thank you for your answer. I handled my problem with your response. — Mohammad Ahmadi, Jan 25 '16 at 22:01

score 1 · Answer 2 · answered Jan 25 '16 at 20:28

1

You have to use sp_executesql to run the query

answered Jan 25 '16 at 20:28

Karim

11
1

Thank you for your response, I search about this function previously, but I cant use it, can you explain more please? – Mohammad Ahmadi Jan 25 '16 at 20:44

How to send tableName and columnName to sql function and return result

2 Answers2