Динамический запрос внутри функции TSQL не в состоянии сделать

Я попытался сделать функцию sql server, которая может генерировать запрос на основе tablename.
Тем не менее, я получаю эту ошибку:

Msg 557, Уровень 16, Состояние 2, Линия 1
Только функции и некоторые расширенные хранимые процедуры могут быть выполнены из функции.

Код как ниже:

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

Я выполняю эту функцию как select * from fn_portal_biu('2016-05-17', '%', 22,'CA', '%', '%','%' ,'%','%','%','%','%','%' )

1 ответ

  1. Почему динамический sql вообще? Нет никаких причин для этого:

    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
    

    это ваш код без и редакции, кроме удаления «динамического» T-sql.

    И да, функции имеют много ограничений по дизайну:
    https://msdn.microsoft.com/en-us/library/ms191320.aspx

    Во всяком случае, написанные предикаты очень странные. Почему «нравится», если нет»нравится»?

    ...
    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 */
    

    кроме того: почему идентификаторы varchar, что должен делать этот код, когда branch_id равен NULL…

    in Я бы предложил переписать в exists
    с таким предикатом, вероятно, лучше добавитьoption(recompile), но я полагаю, что на этих таблицах не так много индексов.