Dynamic Query inside TSQL function not able to do

Question

I tried to make sql server function that can generate query based on tablename. However, i am getting this error:

Msg 557, Level 16, State 2, Line 1 Only functions and some extended stored procedures can be executed from within a function.

Code is as below:

create function fn_test ( @adate varchar(10), @abc_code
nvarchar(100),@b_id nvarchar(100), @c1 nvarchar(100),
                         @own_retained_id nvarchar(100) , @designation02 nvarchar(100), @currency_id nvarchar(100),
                         @Core_ID nvarchar(100), @Slab_ID nvarchar(100), @Product_03_ID nvarchar(100), 
                         @Category_03_ID nvarchar(100), @PRODUCT_CLASS nvarchar(100),@SEGMENT_CODE nvarchar(100) ) returns @t table(wdate
varchar(10) , sale_code nvarchar(100) ,Branch_code nvarchar(100) ,
CASA nvarchar(100),Owned_Retained_ID nvarchar(100),
            Designation02 nvarchar(100),Currency_ID nvarchar(100) ,Core_ID nvarchar(100) , Slab_ID nvarchar(100), Product_03_ID
nvarchar(100),              Category_03_ID nvarchar(100),PRODUCT_CLASS
nvarchar(100),
             SEGMENT_CODE nvarchar(100), CD_Bal_pkr float )

 as   begin  declare @sql nvarchar(max) set  @sql =  N'select cd.wdate
, a.Sale_Code,r.Branch_code ,cd.CASA ,cd.Owned_Retained_ID  ,
r.Designation02 , a.Currency_ID , cd.Core_ID , cd.Slab_ID ,
a.Product_03_ID , a.Category_03_ID, cd.PRODUCT_CLASS,cd.SEGMENT_CODE,
sum(cd.pkr_open_clr_balance) CD_Bal_pkr  from  lov_rm r  left outer
join   xyz CD on CD.MARKETED_BY=R.Sales_Code left outer  join
BIU_Accounts a  on  a.Acct_Number = cd.ACCT_NUMBER   where  
r.Sales_Code = a.Sale_Code and cd.WDATE = ''' +  @wdate + '''  and
a.Sales_Code_ID_Current in (select RM_ID from LOV_RM where
Designation02 = ''SE-CASA'') and r.Sales_Code like case ' + CHAR(39) +
@sales_code + CHAR(39)+' when '''' then ''%'' when ''None'' then ''%''
else ' + CHAR(39) + @sales_code   +CHAR(39) + ' end AND r.Branch_code
like case ' + CHAR(39) + @branch_id +CHAR(39)+' when '''' then ''%''
when ''None'' then ''%'' else ' + CHAR(39) + @branch_id  +CHAR(39) + '
end  AND cd.CASA like case  ' + CHAR(39) + @casa +CHAR(39)+'  when
'''' then ''%'' when ''None'' then ''%'' else ' + CHAR(39) + @casa
+CHAR(39) + '  end  and r.Designation02   like case ' + CHAR(39) + @designation02 +CHAR(39)+'  when '''' then ''%'' when ''None'' then
''%'' else  ' + CHAR(39) + @designation02 +CHAR(39) + '  end  AND
cd.Owned_Retained_ID like case ' + CHAR(39) + @own_retained_id 
+CHAR(39)+' when '''' then ''%'' when ''None'' then ''%'' else ' + CHAR(39) + @own_retained_id +CHAR(39) + '  end  and a.Currency_ID  
like case  ' + CHAR(39) +  @currency_id  +CHAR(39)+' when '''' then
''%'' when ''None'' then ''%'' else ' + CHAR(39) + @currency_id
+CHAR(39) + ' end  and  cd.Core_ID   like case ' + CHAR(39) +  @Core_ID +CHAR(39)+' when '''' then ''%'' when ''None'' then ''%''
else ' + CHAR(39) +  @Core_ID  +CHAR(39) + ' end  and  cd.Slab_ID 
 like case ' + CHAR(39) +  @Slab_ID +CHAR(39)+' when '''' then ''%''
 when ''None'' then ''%'' else  ' + CHAR(39) + @Slab_ID  +CHAR(39) + '
 end  and  a.Product_03_ID  like case  ' + CHAR(39) + @Product_03_ID 
 +CHAR(39)+' when '''' then ''%'' when ''None'' then ''%'' else ' + CHAR(39) + @Product_03_ID +CHAR(39) + ' end  and   a.Category_03_ID 
 like case ' + CHAR(39) + @Category_03_ID +CHAR(39)+' when '''' then
 ''%'' when ''None'' then ''%'' else  ' + CHAR(39) + @Category_03_ID
+CHAR(39) + '  end  and    cd.PRODUCT_CLASS  like case  ' + CHAR(39) + @PRODUCT_CLASS +CHAR(39)+' when '''' then ''%'' when ''None'' then
 ''%'' else ' + CHAR(39) + @PRODUCT_CLASS +CHAR(39) + '  end  and  
 cd.SEGMENT_CODE like case ' + CHAR(39) +@SEGMENT_CODE +CHAR(39)+' 
 when '''' then ''%'' when ''None'' then ''%'' else  ' + CHAR(39) +
 @SEGMENT_CODE +CHAR(39) + '  end

 group by cd.wdate , a.Sale_Code,  r.Branch_code   ,  cd.CASA ,
cd.Owned_Retained_ID  , r.Designation02 ,  a.Currency_ID , cd.Core_ID
 , cd.Slab_ID , a.Product_03_ID , a.Category_03_ID,cd.PRODUCT_CLASS,
 cd.SEGMENT_CODE  ' 


  print  @sql  EXECUTE sp_executesql @sql  return  end

I am executing this function as select * from fn_portal_biu('2016-05-17', '%', 22,'CA', '%', '%','%' ,'%','%','%','%','%','%' )

The error means exactly what it says. You are trying to execute a stored procedure (in this instance, `sp_executesql`) inside a function, which is not allowed. If you need to execute dynamic SQL, do it from within a stored procedure, not a function. — ZLK, May 27 '16 at 05:07
any other way to build dynamic query inside function ? as i need to join function with other function's result to have a joined result . — Cookie, May 27 '16 at 05:36
using stored procedure , can't have a table joining structure .. — Cookie, May 27 '16 at 05:37
You can insert the results of stored procedures into, for example, table variables (essentially doing what your function is doing), then use those table variables to link to other tables/table-valued functions. — ZLK, May 27 '16 at 05:40
I had also faced this problem with SQL user defined functions, I had to convert SQL functions to Stored Procedures as a work-around http://www.kodyaz.com/articles/sql-server-exec-sp_executesql-user-defined-functions.aspx — Eralper, May 27 '16 at 06:15

score 0 · Answer 1 · answered May 27 '16 at 06:40

Why dynamic sql at all? There is no reason for it:

select
    cd.wdate,
    a.Sale_Code,r.Branch_code ,cd.CASA ,cd.Owned_Retained_ID  ,
    r.Designation02 , a.Currency_ID , cd.Core_ID , cd.Slab_ID ,
    a.Product_03_ID , a.Category_03_ID, cd.PRODUCT_CLASS,cd.SEGMENT_CODE,
    sum(cd.pkr_open_clr_balance) CD_Bal_pkr 
from  lov_rm r 
left join xyz CD on CD.MARKETED_BY=R.Sales_Code
left join BIU_Accounts a  on  a.Acct_Number = cd.ACCT_NUMBER
where r.Sales_Code = a.Sale_Code and cd.WDATE = @wdate 
    and a.Sales_Code_ID_Current in (select RM_ID from LOV_RM where Designation02 = 'SE-CASA') 
    and r.Sales_Code like case @sales_code when '' then '%' when 'None' then '%' else @sales_code  end
    and r.Branch_code like case @branch_id when '' then '%' when 'None' then '%' else @branch_id end
    and cd.CASA like case  @casa WHEN '' then '%' when 'None' then '%' else @casa end
    and r.Designation02   like case @designation02 when '' then '%' when 'None' then '%' else @designation02 end
    /* and so on */
 group by cd.wdate , a.Sale_Code,  r.Branch_code,  cd.CASA ,
    cd.Owned_Retained_ID  , r.Designation02 ,  a.Currency_ID , cd.Core_ID,
    cd.Slab_ID , a.Product_03_ID , a.Category_03_ID,cd.PRODUCT_CLASS,
    cd.SEGMENT_CODE

this is your code without and edition except removing "dynamic" t-sql.

And yes, functions have many restrictions by design:
https://msdn.microsoft.com/en-us/library/ms191320.aspx

Anyway predicates written are very strange. Why "like" if there are no "likes"?

...
where r.Sales_Code = a.Sale_Code and cd.WDATE = @wdate 
    and a.Sales_Code_ID_Current in (select RM_ID from LOV_RM where Designation02 = 'SE-CASA') 
    and (@sales_code in ('', 'None') or r.Sales_Code = @sales_code)
    and (@branch_id in ('', 'None') or r.Branch_code = @branch_id)
    and (@casa  in ('', 'None') or cd.CASA = @casa)
    and (@designation02 in ('', 'None') or r.Designation02 = @designation02)
    /* and so on */

furthermore: why id's are varchar, what should this code do when branch_id is NULL...

in I'd suggest to rewrite into exists with suche a predicate it's probably better to add option(recompile) but I suppose there are not much indexes on those tables.

Dynamic Query inside TSQL function not able to do

1 Answers1