How to add inline TVF with dynamic columns from CRL Dynamic Pivot

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  March 9, 2015 at 10:21 am

  #298401

  Hi, I am trying to implement the CLR function Eric Wahner posted in this Article.

  http://qa.sqlservercentral.com/articles/.Net/94922/

  Now, I've pushed the assembly to my DB and can see it there.

  So far I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

  Error:

  Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18

  Incorrect syntax near the keyword 'external'.

  CREATE FUNCTION clrDynamicPivot

  (

  -- Add the parameters for the function here

  @query nvarchar(4000),

  @pivotColumn nvarchar(4000),

  @selectCols nvarchar(4000),

  @aggCols nvarchar(4000),

  @orderBy nvarchar(4000)

  )

  RETURNS TABLE

  AS

  return

  (external NAME CLRfunctions.RIFunctions.clrDynamicPivot)

  GO

• MadAdmin

  SSChampion

  Points: 11260

  More actions

  March 12, 2015 at 11:44 am

  #1783676

  You havent defined columns in the result.

  This example seems to not have syntax errors.

  CREATE FUNCTION clrDynamicPivot

  (

  -- Add the parameters for the function here

  @query nvarchar(4000),

  @pivotColumn nvarchar(4000),

  @selectCols nvarchar(4000),

  @aggCols nvarchar(4000),

  @orderBy nvarchar(4000)

  )

  RETURNS TABLE

  (column1 int,column2 int,column3 varchar(20))

  external NAME CLRfunctions.RIFunctions.clrDynamicPivot;

  Catch-all queries done right [/url]
  Gail Shaw's Performance Blog[/url]