EXECUTE DYNAMIC OPENQUERY INCLUDE VARIABLE IN FUNCTION INPUT-CodePudding

I want to execute a function through a linked server in SQL Server.

When I execute OPENQUERY, it runs successfully, but when I use execute @SQL, it throws an error.

This code runs successfully:

SELECT A 
FROM OPENQUERY([SERVER20], 'SELECT COUNT(9) AS A 
                            FROM MyData.dbo.MyFunctionName(''401700000809723'', null)')

However, this code results in an error:

DECLARE @Sql2 nvarchar(1000) = 'SELECT A FROM OPENQUERY([SERVER20] , ''SELECT COUNT(9) AS A FROM MyData.dbo.MyFunctionName(''''401700000809723'''', null)'')'

EXEC @Sql2

This code also results in an error:

DECLARE @PolicyKey varchar(50) = '401700000809723'
DECLARE @Sql3 nvarchar(1000) = 'SELECT A FROM OPENQUERY([SERVER20] , ''SELECT COUNT(9) 
   AS A FROM MyData.dbo.MyFunctionName('''''  @PolicyKey  ''''', null)'')' 

EXEC @Sql3

Error in case 2 and 3 is:

Msg 203, Level 16, State 2, Line 38
The name 'SELECT A FROM OPENQUERY([SERVER20] , 'SELECT COUNT(9) AS A FROM MyData.dbo.MyFunctionName(''401700000809723'', null)')' is not a valid identifier.

CodePudding user response：

I think variable query is fine that is stored in a variable, but problem with execution:

EXEC (@Sql2);