Help Needed in Sql Function

Transact-SQL
1

Hi,

I have "Mark" column(int) in table "Sample" and i need to create a function whihc should accept three parameters colunName, tableName and condition.
The logic inside the fucntion would be to calculate the average with rounding to the nearest based on the input patameters. This function has to be generic which can be used in various places of my school application.

the sample call : select dbo.CalculateSVG('mark','student','studentmark>50')

Please help me on creating gthe function for the above logic to make accurate avg with rounding.

Thanks

2

to do that you will required Dynamic SQL but you can't use Dynamic SQL in a fucntion. You will need to use stored procedure

3

Thanks Khtan and it would more helpful to have the procedure. I am interested to know the logic. could you please post the sql script for this using procedure.

Thanks

4 
declare @sql nvarchar(max)
select @sql = N'select avg (' + @column + ') '
            + N'from ' + @table + ' '
            + N'where ' + @condition

exec sp_executesql @sql
1 Like
5

I reckon this is a really bad idea - I sure hope this isn't an assignment as, for me, it would call into question why the Teacher is setting this as an example ...

If it is a Real World Application then I suspect that this may well be due to the database structure, which could do with a rethink, or the reporting requirements which could be handled differently / better (including using Dynamic SQL and sp_ExecuteSQL to get great performance on adhoc queries, if that is what the requirement turns out to be.

6

thakns guys for the reply,

7

And before jumping into Dynamic SQL, make sure to read this http://www.sommarskog.se/dynamic_sql.html