sql - dynamic Procedure, remove last string chars -
i have parameter dynamically generates union depends on input.
select @cmd +=n' set nocount on; select count(case when rfrd.queutypeid=4 rfrd.queutypeid end) requiredaqueue ,count(distinct rfrd.leaddocumentid) repeat,concat('''+rf+''' ,'' '') riskfactorid fct.riskmng_fact_riskfactorresultdetails rfrd union select count(case when rfrd.queutypeid=4 rfrd.queutypeid end) requiredaqueue ,count(distinct rfrd.leaddocumentid)as repeat,concat('''+rf+''' ,''*'') riskfactorid fct.riskmng_fact_riskfactorresultdetails rfrd' -------------- #cte rf exec sys.sp_executesql @cmd at end want connect them 1 long union, added union in end:
select @cmd +=n' set nocount on; select count(case when rfrd.queutypeid=4 rfrd.queutypeid end) requiredaqueue ,count(distinct rfrd.leaddocumentid) repeat,concat('''+rf+''' ,'' '') riskfactorid fct.riskmng_fact_riskfactorresultdetails rfrd union select count(case when rfrd.queutypeid=4 rfrd.queutypeid end) requiredaqueue ,count(distinct rfrd.leaddocumentid)as repeat,concat('''+rf+''' ,''*'') riskfactorid fct.riskmng_fact_riskfactorresultdetails rfrd' -------------- +' union ' <----------------------- #cte rf exec sys.sp_executesql @cmd after runs, last union needless,
, errors:
on ou.id = rfrd.entity_organizationunitid inner join #entity_organizationunittypeids out on out.id = rfrd.entity_organizationunittypeid rfrd.entitytypeid = 1055 , rfrd.riskfactorid in(6249,6102) , rfrd1.id null union msg 156, level 15, state 1, line 118
can suggest how can remove last union all? maybe should not add union al @ end? thanks
at end need remove by
set @cmd = substring(@cmd,1,len(@cmd)-9) exec sys.sp_executesql @cmd
Comments
Post a Comment