How can I avoid calling a stored procedure from a UDF in SQL Server -

before start, know can't call stored procedure udf , know there various "reasons" (none make sense me though, tbh sounds laziness on microsoft's part).

i more interested in how can design system round flaw in sql server.

this quick overview of system have:

• i have dynamic report generator users specify data items, operators (=, <, !=, etc.) , filter values. these used build "rules" 1 or more filters, e.g. might have rule has 2 filters "category < 12" , "location != 'york'";

• there thousands , thousands of these "rules", of them have many, many filters;

• the output each of these rules statuory report has same "shape", i.e. same columns/ data types. these reports produce lists of tonnages , materials;

• i have scalar-valued function generates dynamic sql specified rule, returning varchar(max);

• i have stored procedure called run specific rule, calls udf generate dynamic sql, runs , returns results (this used return results store output in process-keyed tables make data easier share , return handle data instead);

• i have stored procedure called run rules particular company, makes list of rules run, runs them sequentially , merges results output.

so works perfectly.

now want 1 final thing, report runs company summary , applies costs tonnages/ materials result in cost report. seemed such simple requirement when started on last week :'(

my report has table-valued function work report broker system have written. if write stored procedure not run through report broker means not controlled, i.e. won't know ran report , when.

but can't call stored procedure within table-valued function , 2 obvious ways handle follows:

1. get sql create output, run , suck results.

   --method #1 while @ruleindex <= @maxruleindex begin declare @dsfid uniqueidentifier; select @dsfid = [guid] newguid; --this has deterministic, isn't compiler thinks , that's enough :d declare @ruleid uniqueidentifier; select @ruleid = dsfruleid @dsfrules dsfruleindex = @ruleindex; declare @sql varchar(max);  --get sql select @sql = dsf.dsfengine(@serviceid, @memberid, @locationid, @dsfyear, null, null, null, null, @dsfid, @ruleid);  --run execute(@sql);  --copy data out of results table our local copy insert      @dsfresults  select      tableid, tablecode, tablename, rowid, rowname, locationcode, locationname, productcode, productname, packaginggroupcode, packaginggroupname, levelname, weightsource, quantity, paper, glass, aluminium, steel, plastic, wood, other, 0 general       dsf.dsfpackagingresults       dsfid = @dsfid      , ruleid = @ruleid; select @ruleindex = @ruleindex + 1; end; 

2. call report directly

   --method #2 while @ruleindex <= @maxruleindex begin  declare @dsfid uniqueidentifier; select @dsfid = [guid] newguid; --this has deterministic, isn't compiler thinks :d declare @ruleid uniqueidentifier; select @ruleid = dsfruleid @dsfrules dsfruleindex = @ruleindex; declare @sql varchar(max);  --run report execute executedsfrule @serviceid, @memberid, @locationid, @dsfyear, null, null, null, @ruleid, @dsfid, 2;  --copy data out of results table our local copy insert      @dsfresults  select      tableid, tablecode, tablename, rowid, rowname, locationcode, locationname, productcode, productname, packaginggroupcode, packaginggroupname, levelname, weightsource, quantity, paper, glass, aluminium, steel, plastic, wood, other, 0 general       dsf.dsfpackagingresults       dsfid = @dsfid      , ruleid = @ruleid; select @ruleindex = @ruleindex + 1; end; 

i can think of following workarounds (none of particularly satisfactory):

• rewrite of in clr (but whole lot of hassle break rules);

• use stored procedure produce report (but means lose control of execution unless develop new system single report, different dozens of existing reports work fine);

• split execution reporting, have 1 process execute report , picks output (but no way tell when report has completed without more work);

• wait until microsoft see sense , allow execution of stored procedures udfs.

any other ideas out there?

---

edit 3-may-2013, here (very) simple example of how hangs together:

--data reported create table datatable (     memberid int,     productid int,     productsize varchar(50),     imported int,     [weight] numeric(19,2)); insert datatable values (1, 1, 'large', 0, 5.4); insert datatable values (1, 2, 'large', 1, 6.2); insert datatable values (1, 3, 'medium', 0, 2.3); insert datatable values (1, 4, 'small', 1, 1.9); insert datatable values (1, 5, 'small', 0, 0.7); insert datatable values (1, 6, 'small', 1, 1.2);  --report headers create table reportstable (     reporthandle int,     reportname varchar(50)); insert reportstable values (1, 'large products'); insert reportstable values (2, 'imported small products');  --report detail create table reportsdetail (     reporthandle int,     reportdetailhandle int,     databasecolumn varchar(50),     datatype varchar(50),     operator varchar(3),     filtervalue varchar(50)); insert reportsdetail values (1, 1, 'productsize', 'varchar', '=', 'large'); insert reportsdetail values (2, 1, 'imported', 'int', '=', '1'); insert reportsdetail values (2, 1, 'productsize', 'varchar', '=', 'small'); go create function generatereportsql (     @reporthandle int) returns varchar(max) begin     declare @sql varchar(max);     select @sql = 'select sum([weight]) datatable 1=1 ';     declare @filters table (         filterindex int,         databasecolumn varchar(50),         datatype varchar(50),         operator varchar(3),         filtervalue varchar(50));     insert @filters select row_number() on (order databasecolumn), databasecolumn, datatype, operator, filtervalue reportsdetail reporthandle = @reporthandle;     declare @filterindex int = null;     select top 1 @filterindex = filterindex @filters;     while @filterindex not null     begin         select top 1 @sql = @sql + ' , ' + databasecolumn + ' ' + operator + ' ' + case when datatype = 'varchar' '''' else '' end + filtervalue + case when datatype = 'varchar' '''' else '' end @filters filterindex = @filterindex;         delete @filters filterindex = @filterindex;         select @filterindex = null;         select top 1 @filterindex = filterindex @filters;     end;     return @sql; end; go create procedure executereport (     @reporthandle int) begin     --get sql     declare @sql varchar(max);     select @sql = dbo.generatereportsql(@reporthandle);     execute (@sql); end; go --test execute executereport 1; execute executereport 2; select dbo.generatereportsql(1); select dbo.generatereportsql(2); go --what want create function runreport (     @reporthandle int) returns @results table ([weight] numeric(19,2)) begin     insert @results execute executereport @reporthandle;     return; end; --invalid use of side-effecting operator 'insert exec' within function 

if in situation, wouldn't try hack anything. setup objects this:

create table [dbo].[reportcollection] (     [reportcollectionid] int,     [reportid] int )  create table [dbo].[reportresult] (     [reportid] int,     [locationcode] int,     [locationname] nvarchar(max) )  create procedure [dbo].[usp_executereport] (     @reportid int )     insert [dbo].[reportresult]     select @reportid, 1, n'stackoverflow' end  create function [dbo].[udf_retrievereportcollectionresults] (     @reportcollectionid int ) returns @results table ([reportid], [locationcode], [locationname]) begin     select *     [dbo].[reportresult] rr     join [dbo].[reportcollection] rc         on rr.reportid = rc.reportid     rc.reportcollectionid = @reportcollectionid end 

and use them this:

insert [dbo].[reportcollection] values (1, 1) insert [dbo].[reportcollection] values (1, 2)  exec [dbo].[usp_executereport] @reportid = 1 exec [dbo].[usp_executereport] @reportid = 2  select * [dbo].[udf_retrievereportcollectionresults](1) 

each time run reports, start new collection. application should kick off of reports , consolidate results afterward.

--

if really wanted call stored procedure udf (please don't), search on xp_cmdshell.