-1

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

Parth M. Dave
  • 853
  • 1
  • 5
  • 16
  • *"newly added table type field"* What do you mean by this comment? "Fields" aren't something in SQL Server. Some people misuse the word to mean column, but a column cannot be defined as `table` type they can only be a scalar value. – Thom A Jul 04 '23 at 10:29
  • 2
    `CostPrice float` this is the *completely* wrong data type for a "price". Monetary values can (and often are) non-integer values and a base-2 value **cannot** represent a base-10 value accurately when the value is a non-integer. – Thom A Jul 04 '23 at 10:30
  • Also why are you using dynamic SQL at all here? There's nothing dynamic about your query, you're just *injecting* your parameter(s), where instead you should be **parametrising**. There appears to be no reason to use dynamic SQL here, and if you are, `EXEC(@SQL)` is *not* the correct way to execute a parameterised statement; you should be using a **parametrised** call to `sys.sp_executesql`. – Thom A Jul 04 '23 at 10:35
  • *"Above approach give me error"* you also forgot to include that error. – Thom A Jul 04 '23 at 10:39
  • If you use PRINT @SQL in place of EXEC and then compare the string you've created against the original SQL statement (the one that works), you'll see that the 4th value, in your insert statement is a single apostrophe, instead of a double, because you haven't escaped it properly. i.e. 'AAAA',1,1, ' ,'2023-03-07' instead of 'CADC',1,1, '' ,'2023-03-07' – Celador Jul 04 '23 at 10:41
  • What does `PROC_GET_ITEM_WEIGHTED_AVERAGE_COST` do? It's probably much easier to just `CROSS APPLY` your function to whatever that is doing. Please also show what your function is doing, and explain why you need dynamic SQL here. – Charlieface Jul 04 '23 at 10:52
  • @ThomA "newly added table type field" means this parameter is added to function which you can not found in above code which is mention & your next comment is useful for me which is sys.sp_executesql. sorry guyz i forgot to mention error about "Above Approach give me error". – Parth M. Dave Jul 05 '23 at 08:04
  • Don't call parameters "fields", @ParthM.Dave , that is *very* confusing. As I mentioned, "field" isn't a term for anything in SQL Server, and the most common (mis)uses for it are for a column, or a specific value in a column and row. A Table Type Parameter (TVP) certainly isn't a "field", as in other uses it denotes a scalar value, and a TVP can contain but many columns and rows. – Thom A Jul 05 '23 at 08:08
  • @ThomA Understand your point thank you for your great guidance. – Parth M. Dave Jul 05 '23 at 08:09

2 Answers2

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

siggemannen
  • 3,884
  • 2
  • 6
  • 24
-1

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
Parth M. Dave
  • 853
  • 1
  • 5
  • 16