0

I wrote a function (dbo.fFunctionCols) with 2 parameters (1st is tablename, 2nd is search pattern for column names) that returns certain column names from a table and passes them to the query below in which the same table is unpivoted. The query should end up as a view.

SELECT Id, AB, vTime, vItem, vCount
FROM
   (SELECT Id, AB, vTime,
    dbo.fFunctionCols('tablename', 'abc%def')
    FROM dbo.[tablename]) AS piv
UNPIVOT
   (vCount FOR vITEM IN
      (SELECT dbo.fFunctionCols('tablename', 'abc%def') AS t1
   ) AS unpiv;
GO

SQL Server always returns:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

The statement: SELECT dbo.fFunctionCols('tablename', 'abc%def') AS t1 works properly if executed. It delivers the correct list of the relevant column names seperated with commas and enclosed in square brackets.

What´s the error? Why can the function not be used inside the IN operator?

seagull
  • 1
  • 1
  • 1
    Assuming just a typo here SLECT ... However, SQL Server does not support macro substitution. – John Cappelletti Sep 27 '22 at 18:01
  • 1
    Take a peek at https://stackoverflow.com/questions/73691009/select-table-column-names-as-values/73691087#73691087 this will allow you to dynamically UNPIVOT virtually any table, view and/or query WITHOUT dynamic SQL. – John Cappelletti Sep 27 '22 at 18:04

0 Answers0