Create a table valued function

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 20, 2014 at 11:52 am

  #311955

  Hi I would like to create a table valued function using the following data:

  create table #WeightedAVG

  (

  Segment varchar(20),

  orders decimal,

  calls int

  );

  insert into #WeightedAVG

  SELECT 'L2','13','455' UNION ALL

  SELECT 'L2','5','551' UNION ALL

  SELECT 'L2','4','965' UNION ALL

  SELECT 'L1','4','2750' UNION ALL

  SELECT 'L1','11','4155' UNION ALL

  SELECT 'L2','2','3121' UNION ALL

  SELECT 'L2','19','9435' UNION ALL

  SELECT 'L2','2','10792' UNION ALL

  SELECT 'L1','11','3379' UNION ALL

  SELECT 'L1','7','4763' UNION ALL

  SELECT 'L1','52','26948' UNION ALL

  SELECT 'L1','10','3927' UNION ALL

  SELECT 'L1','14','13492' UNION ALL

  SELECT 'L2','3','2296' UNION ALL

  SELECT 'L2','62','9705' UNION ALL

  SELECT 'L1','5','1099' UNION ALL

  SELECT 'L1','10','2570' UNION ALL

  SELECT 'L1','25','5540' UNION ALL

  SELECT 'L1','40','25479' UNION ALL

  SELECT 'L1','9','10948' UNION ALL

  SELECT 'L2','79','10679' UNION ALL

  SELECT 'L3','1','1828' UNION ALL

  SELECT 'L1','1','28324' UNION ALL

  SELECT 'L1','1','14562' UNION ALL

  SELECT 'L1','26','1248' UNION ALL

  SELECT 'L1','45','8875' UNION ALL

  SELECT 'L2','8','3534' UNION ALL

  SELECT 'L2','12','5139' UNION ALL

  SELECT 'L2','2','407' UNION ALL

  SELECT 'L1','89','9551'

  I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,

  CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)

  returns @Result table

  (

  col1 varchar(25),

  WeightedAVG float

  )

  AS

  BEGIN

  insert into @Result

  SELECT @columns, (@divisor / @dividend) as 'WeightedAVG' from @table group by @columns

  return

  END

  select WeightedAVG(80, 10, #WeightedAVG, Segment)

  Hope this makes sense ...

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 20, 2014 at 12:01 pm

  #1753593

  You can't do this. To be able to query different tables and columns, you need to use dynamic code. Dynamic code can be dangerous if it's not handled correctly and it's not allowed in functions.

  By the way, you should try to use inline table valued functions to avoid performance problems.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  October 20, 2014 at 12:24 pm

  #1753598

  Quick question, could you describe the problem you are trying to solve?

  😎

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 20, 2014 at 12:28 pm

  #1753599

  This is an example on how to do it through dynamic code.

  DECLARE @divisor int = 80,

  @dividend int = 10,

  @table varchar(128) = '#WeightedAVG',

  @columns varchar(8000) = 'Segment'

  DECLARE @SQL nvarchar(max);

  --This is meant to prevent sql injection on column names.

  SET @columns = STUFF((SELECT ', ' + QUOTENAME(Item)

  FROM DelimitedSplit8K(@Columns, ',')

  ORDER BY ItemNumber

  FOR XML PATH('')), 1, 2, '');

  --The code and explanation for DelimitedSplit8K can be found in here:

  --http://www.sqlservercentral.com/articles/Tally+Table/72993/

  --Create the dynamic code

  SET @SQL = 'SELECT ' + @columns + '

  , (@divisor / @dividend) as ' + QUOTENAME(REPLACE( @table, '#', ''), '''') + '

  FROM ' + QUOTENAME( @table) + '

  GROUP BY ' + @columns + ';';

  --This is used to debug the code

  --PRINT @SQL;

  --Execute a parametrized query

  EXEC sp_executesql @SQL, N'@divisor int, @dividend int', @divisor, @dividend;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 20, 2014 at 12:41 pm

  #1753600

  Hi Erik, I would like to create a function that would give me a 'weighted average' total number * .weighting ie, silver is worth 40% of gold so silver = value_of_gold($80) * value_weighting(40%) so a simple function would look like

  select * from weightAvg(80, 40)

  but the data will be coming from a table so there will be additional columns, ie, city, retailer, month so the I would like to be able to run a function that will compute a table with different columns which will also need groupings ...