Share via

SQL Server: Using Parameterized Functions

  • Article

A Transact SQL Forum topic we see is  Problems using functions in SQL server:           
How to execute dynamic sql from function
 
Actually, SQL server has limitations in functions. One of the limitations of custom functions is parameters can not be transferred to name of the table function.

For example, we have 20 tables that we need to work 20 functions. For this problem we thought about " xp_exec".

For example, first created a table and dynamically selected columns from this table:

use tempdb
go
create table  test
(t1 int)
go
insert test values(1)
insert test values(2)
insert test values(2)
insert test values(3)
insert test values(3)
insert test values(4)
insert test values(5)
insert test values(8)
 
go

And then we will create a function for given results dynamically:

drop function  showfunction
go
create function  dbo.showfunction(@tablename sysname,@colname sysname)
returns @showtable table (showresult sql_variant)
as
begin
declare @stringsql varchar(8000)
set @stringsql='
Create Function dbo.showprim()
Returns @showtab Table(showres sql_variant)
as
begin
insert @showtab
Select showres=AVG(t1) from (
select min(t1) as t1 from (
select top 50 percent '+@colname+' as t1 from '+@tablename+' order by t1 desc
)t
union all
select max(t1) from (
select top 50 percent '+@colname+' as t1 from '+@tablename+' order by t1
)t
)M
Return
end
'
 
exec master..xp_exec 'Drop Function Dbo.showprim','N','tempdb'
exec master..xp_exec @stringsql,'N','tempdb'
Insert @showtable Select*from showprim()
return
end
go
 
select* from  showfunction('test','t1')
go
drop table  test
 
showresult 
----------------
3

Results was successful.