cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
verstreater86
New Member

Dynamic SQL in User Defined Function for Direct Query

I received the DECLARE error message stating I can't use declare in a Direct Query. I found out in this forum that a user defined function (UDF) is necessary to make this work: https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/td-p/40983

I then found out from the error message that I can't have dynamic SQL in a UDF.

"Invalid use of a side-effecting operator 'EXECUTE STRING' within a function."

 

Are there any other options for getting my query to work in a direct query in Power BI? My query is below:

 

DECLARE @SQL as VARCHAR(MAX)
DECLARE @Columns AS VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','')+QUOTENAME([MonthYear])
	FROM( SELECT distinct cast([MonthName] as varchar) + ' ''' + right(cast([Year] as varchar),2) AS 
				MonthYear, [Year],[Month]
			FROM gpo.dbo.DateDimension A WHERE [Date] between getdate()-365 AND dateadd(month,-1,getdate())) A  order by [Year],[Month]  

SET @SQL = '
	WITH PivotData AS
		(
			SELECT distinct
				A.[Client Designated Loc Nm] as Client,
				sum(A.TotalInvoice) as [Total Admin Fee],  
				cast([MonthName] as varchar) + '' '''''' + right(cast([Year] as varchar),2) AS MonthYear 
			FROM GPO.DBO.SALES A
			INNER JOIN
			GPO.DBO.datedimension B
			ON A.InvoiceDate=B.[Date]
			INNER JOIN 
			RXSS.DBO.SupplierContractNbrs C
			ON A.SupplierID=C.SupplierID
			WHERE A.[Status] = ''A'' and C.ActiveReporting = 1 AND B.[Date] >= getdate()-365
			GROUP BY 
				A.SupplierID,
				A.[Client Designated Loc Nm],
				A.InvoiceDate,
				B.[MonthName],
				B.[Year],
				B.[MonthYear] 
		) 
	SELECT * FROM (SELECT Client, ' + @Columns + ' FROM PivotData 
	PIVOT
		(
			sum([Total Admin Fee])
			FOR 
				[MonthYear] 
			IN 
				( 
					' + @Columns + '
				) 
		) AS pvt ) D 
		'
EXEC (@SQL)

 

2 REPLIES 2
Microsoft
Microsoft

Re: Dynamic SQL in User Defined Function for Direct Query

Hi @verstreater86

 

What about calling the sp_executeSQL proc and passing your dynamic query via that?

 

Worked for me using Direct Query

 

sp_executeSQL.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft
Microsoft

Re: Dynamic SQL in User Defined Function for Direct Query


@verstreater86 wrote:

I received the DECLARE error message stating I can't use declare in a Direct Query. I found out in this forum that a user defined function (UDF) is necessary to make this work: https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/td-p/40983

I then found out from the error message that I can't have dynamic SQL in a UDF.

"Invalid use of a side-effecting operator 'EXECUTE STRING' within a function."

 

Are there any other options for getting my query to work in a direct query in Power BI? My query is below:

  


  1. That error is due to SQL Server User Defined Function limitation, you can't execute dynamic sql in an UDF.
  2. That dynamical PIVOT would return dynamic columns, I don't think it is a good practice to use dynamic metadata.
  3. AFAIK, there's no way but switching to import mode.

May I know why dynamical PIVOT in your case? What is the final goal?

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors