SQL SERVER execute user define function using Table Type value as parameter + dynamic query execution

Question

I have issue with dynamic query execution in sql server. I have a sql function FUNC_STOCK_VALUATION_COST_TTYPE which gives me result. I am calling this function. using below method:

DECLARE @DynamicParameterValue AS Nvarchar(MAX)= '''2023-07-04'', ''105'', ''2023-07-04'', 10, ''AAAA'',1, 1, 1, 0';
Declare @SQL As nvarchar(max) = 'SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE](' + REPLACE(@DynamicParameterValue,'"','') +  ')'

EXEC(@SQL)

Above approach work as expected. Now I need to add one more argument in above user define function as Table Type. User Define function Argument list as below:

CREATE FUNCTION [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE]                                                                                                  
(@FromDate date,                                                                        
@OrganizationCode Nvarchar(MAX),                                                   
@ToDate date,                                              
@GroupCode int,                                                    
@LegalOrganizationKey Nvarchar(60),                                                   
@IsCharacteristicFilter Tinyint,                                                  
@IsOrganizationFilter Tinyint,                                              
@CostMethod Tinyint,         
@CostEvaluateMethod Tinyint,
@CostTable dbo.TTCostTable READONLY     --- < newly added table type field....
)

Table Type Defination as below:

CREATE TYPE dbo.TTCostTable AS TABLE (
    ID int,
    Code int,
    DocumentDate date,
    ItemCode int,
    CostPrice float,
    Quantity float,
    Amount float,
    SequenceNo int,
PRIMARY KEY ( ID ),
INDEX IX_ObjectId_OperatorType ( ItemCode, DocumentDate desc )
);

Now using Above table type I have call above function using below code:

DECLARE @CostTable AS TTCostTable;
Insert Into @CostTable Exec PROC_GET_ITEM_WEIGHTED_AVERAGE_COST 'CADC',1,1,'','2023-03-07'    
SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE]('2023-07-04','105','2023-07-04',10,'AAAA',1,1,1,0,@CostTable)

Above code work as expected. Now main problem is i am not use above code for dynamic query creation. my efforts for doing this is as below:

Declare @SQL As nvarchar(max) = '
DECLARE @CostTable AS TTCostTable;
Insert Into @CostTable Exec PROC_GET_ITEM_WEIGHTED_AVERAGE_COST ''AAAA'',1,1,'',''2023-03-07''
DECLARE @DynamicParameterValue AS Nvarchar(MAX)= ''2023-07-04'', ''105'', ''2023-07-04'', 10, ''AAAA'',1, 1, 1, 0,@CostTable
SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE] (@DynamicParameterValue) )'

EXEC(@SQL)

Above approach give me error

Answer 1

Your problem is this:

''AAAA'',1,1,'',''2023-03-07''

it should've been

''AAAA'',1,1,'''',''2023-03-07''

ie, you need to add another set of quotes.

With that being said, nothing in your code needs dynamic SQL at all, so unless you're going for some sort of style points, i'd say you should remove it

Answer 2

My issue solve by below way & in below question & ans i have mention DynamicaParameterValue which is come from another Process but for here display simplified i have provided as a static:

DECLARE @DynamicParameterValue AS Nvarchar(MAX)= '''2023-07-04'', ''105'', ''2023-07-04'', 10, ''AAAA'',1, 1, 1, 0'+','+ '@CostTable'
Declare @SQL As nvarchar(max) = N'SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE] '+ '(' + REPLACE(@DynamicParameterValue,'"','') +  ')'
print @SQL
exec sp_executesql @SQL,N'@CostTable TTCostTable readonly',@CostTable=@CostTable