T-SQL pass column to a function as parameter

Question

I have a table with multiple value columns. values can be an integer or NULL. example:

LFNR    WertA   WertB    WertC
1       1       1          2
2      100     100       200
3      NULL    1        NULL
5      1       NULL     1
6      0       0        0
40    NULL     1        NULL

I need to get the average of lets say top 3 values beeing not NULL for each column. For a single column I get the desired average with this statement:

SELECT AVG(WertA) FROM (SELECT TOP 3 WertA FROM synta_rollmw WHERE WERTA IS NOT NULL ORDER BY lfnr DESC)u

To use this value in a SSRS, my idea was to use a function like this:

SELECT dbo.func_avg_Wert(WertA), dbo.func_avg_Wert(WertB), ...

Here is my code for the function:

CREATE FUNCTION dbo.func_avg_Wert (@col_in varchar(15))

RETURNS int
AS
BEGIN

    DECLARE @rollmw int
    DECLARE @sqlquery VARCHAR(1000)

    SET @sqlquery = ('SELECT AVG(@col_in) FROM (SELECT TOP 3 @col_in FROM synta_rollMW WHERE @col_in IS NOT NULL ORDER BY lfnr DESC)u') 
    EXEC @sqlquery
    RETURN @rollmw

END
GO

But if I try to use the function, I get "Column name 'WertA' not valid. What is wrong or is there a better way ? Thanks

You also won't be able to do this as 'EXEC' isn't allowed in functions as functions aren't allowed to have side effects and 'EXEC' allows side-effects; see http://stackoverflow.com/q/9607935/130352 — Chris J, Oct 14 '13 at 09:06
This is usually a sign that what you're currently storing in 3 columns ought to be stored in a single column (across more rows) and an additional column would hold `A`, `B` and `C` - that is, you seem to have ended up with data you want to write queries about embedded in *names* (metadata). — Damien_The_Unbeliever, Oct 14 '13 at 09:28

score 0 · Accepted Answer · answered Oct 14 '13 at 12:07

As bummi says, using a CASE lets you write a function.

CREATE FUNCTION dbo.func_avg_Wert (@col_in varchar(15))
RETURNS int
AS
BEGIN
  DECLARE @A INT;
  WITH ColTbl AS
  (
    SELECT LFNR,
      CASE @Col_In
        WHEN 'WertA' THEN WertA
        WHEN 'WertB' THEN WertB
        WHEN 'WertC' THEN WertC
      END AS Wert
    FROM synta_rollmw
  )
  SELECT @A = AVG(Wert) 
  FROM 
  (
      SELECT TOP(3) Wert 
      FROM ColTbl 
      WHERE Wert IS NOT NULL 
      ORDER BY lfnr DESC
  )u;
  RETURN @A;
END;

You then need quotes around the column names when you call it:

SELECT dbo.func_avg_Wert('WertA'), dbo.func_avg_Wert('WertB'), dbo.func_avg_Wert('WertC');

But, as Damien_The_Unbeliever says, wanting to do this likely means your table is mis-designed.

T-SQL pass column to a function as parameter

1 Answers1