SQL Server facing problem to compile function

Question

SQL Server facing problem to compile function

Sudip Bhatt 2,281

This is my function code which is not getting compile. error is not clear. please tell me what to change in code.
can't we return result set executing dynamic sql ?

thanks

CREATE FUNCTION Fn_GetDelTaData 
(  
    @Ticker VARCHAR(20)
)
RETURNS TABLE 
AS

 BEGIN
 DECLARE @Columns as VARCHAR(MAX),@SQL VARCHAR(MAX)
 SELECT @Columns =
 COALESCE(@Columns + ', ','') + QUOTENAME(Broker)
 FROM
    (SELECT DISTINCT Broker
 FROM tblOutputDetl_csmtuner where Ticker=@Ticker AND TRIM(Broker)<>''
    ) AS B
 ORDER BY B.Broker

 SET @SQL ='SELECT * FROM   
 (
 SELECT 
 Section, 
 LineItem,
 Broker,
 ItemValue_NoFormat
 FROM 
 tblOutputDetl_csmtuner where Ticker='''+@Ticker+'''
 ) t 
 PIVOT(
 Max(ItemValue_NoFormat) 
 FOR Broker IN ('+@Columns+')
 ) AS pivot_table'

 RETURN EXEC(@SQL)
 END

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Tom Phillips 17,786

You cannot use dynamic sql or EXEC inside a function.
Please see:
https://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/

Sudip Bhatt 2,281 Reputation points

2021-02-03T15:23:27.733+00:00

i have visited your link but not clear what to change in my code. please highlight the area what i need to change in my posted code.

Erland Sommarskog 134.7K MVP Volunteer Moderator

You need to take out this code:

SET @SQL ='SELECT * FROM   
  (
  SELECT 
  Section, 
  LineItem,
  Broker,
  ItemValue_NoFormat
  FROM 
  tblOutputDetl_csmtuner where Ticker='''+@Ticker+'''
  ) t 
  PIVOT(
  Max(ItemValue_NoFormat) 
  FOR Broker IN ('+@Columns+')
  ) AS pivot_table'

  RETURN EXEC(@SQL)

Well, more generally, you need to delete everything and start over. You can't do that in a function. Full stop.

Share via

SQL Server facing problem to compile function

0 additional answers

Your answer