Geek Answers Handbook

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

Before you begin, I know that you cannot call a stored procedure from UDF, and I know that there are different “reasons” for this (none of them make any sense to me, tbh, it just sounds like laziness on Microsoft part).

I'm more interested in how I can develop a system to get around this drawback in SQL Server.

This is a brief overview of the system I currently have:

  • I have a dynamic report generator where users set data items, operators (=, <,! =, Etc.) and filter values. They are used to create “rules” with one or more filters, for example. I may have a rule in which there are two filters: "Category <12" and "Location! =" York ";

  • there are thousands and thousands of these "rules", some of which contain many filters;

  • the conclusion from each of these rules is a statutory report, which always has exactly the same “form”, that is, the same columns / data types. Basically, these reports contain lists of tonnages and materials;

  • I have a scalar function that generates Dynamic SQL for the specified rule, returning it as VARCHAR (MAX);

  • I have a stored procedure that is called to run a specific rule, it calls UDF to generate dynamic SQL, runs it and returns the results (this is used to just return the results, but now I save the output in tables with a key to simplify data transfer, and therefore I return a handle to this data);

  • I have a stored procedure that is called to run all the rules for a particular company, so it creates a list of rules to run, runs them sequentially, and then combines the results together as output.

So everything works fine.

Now I want one last thing, a report that summarizes the company, and then the tonnage / material costs are applied to get the expense report. It seemed such a simple requirement when I started this last week: "(

My report should be a table function for working with the brokerage reporting system that I already wrote. If I write it as a stored procedure, it will not be launched through my report broker, which means that it will not be controlled, i.e. I do not know who launched the report and when.

But I cannot call a stored procedure from a table function and there are two obvious ways to handle it:

  • Get SQL to create the output, run it and suck the results.

    --Method #1 WHILE @RuleIndex <= @MaxRuleIndex BEGIN DECLARE @DSFId UNIQUEIDENTIFIER; SELECT @DSFId = [GUID] FROM NewGUID; --this has to be deterministic, it isn't but the compiler thinks it is and that good enough :D DECLARE @RuleId UNIQUEIDENTIFIER; SELECT @RuleId = DSFRuleId FROM @DSFRules WHERE DSFRuleIndex = @RuleIndex; DECLARE @SQL VARCHAR(MAX); --Get the SQL SELECT @SQL = DSF.DSFEngine(@ServiceId, @MemberId, @LocationId, @DSFYear, NULL, NULL, NULL, NULL, @DSFId, @RuleId); --Run it EXECUTE(@SQL); --Copy the data out of the results table into our local copy INSERT INTO @DSFResults SELECT TableId, TableCode, TableName, RowId, RowName, LocationCode, LocationName, ProductCode, ProductName, PackagingGroupCode, PackagingGroupName, LevelName, WeightSource, Quantity, Paper, Glass, Aluminium, Steel, Plastic, Wood, Other, 0 AS General FROM DSF.DSFPackagingResults WHERE DSFId = @DSFId AND RuleId = @RuleId; SELECT @RuleIndex = @RuleIndex + 1; END;

  • Report Direct Call

     --Method #2 WHILE @RuleIndex <= @MaxRuleIndex BEGIN DECLARE @DSFId UNIQUEIDENTIFIER; SELECT @DSFId = [GUID] FROM NewGUID; --this has to be deterministic, it isn't but the compiler thinks it is :D DECLARE @RuleId UNIQUEIDENTIFIER; SELECT @RuleId = DSFRuleId FROM @DSFRules WHERE DSFRuleIndex = @RuleIndex; DECLARE @SQL VARCHAR(MAX); --Run the report EXECUTE ExecuteDSFRule @ServiceId, @MemberId, @LocationId, @DSFYear, NULL, NULL, NULL, @RuleId, @DSFId, 2; --Copy the data out of the results table into our local copy INSERT INTO @DSFResults SELECT TableId, TableCode, TableName, RowId, RowName, LocationCode, LocationName, ProductCode, ProductName, PackagingGroupCode, PackagingGroupName, LevelName, WeightSource, Quantity, Paper, Glass, Aluminium, Steel, Plastic, Wood, Other, 0 AS General FROM DSF.DSFPackagingResults WHERE DSFId = @DSFId AND RuleId = @RuleId; SELECT @RuleIndex = @RuleIndex + 1; END;

I can come up with the following workarounds (none of which are particularly satisfactory):

  • rewrite part of this in the CLR (but it’s just a lot of trouble to break the rules);

  • use the stored procedure to create my report (but this means that I lose control over the execution if I do not develop a new system for this SINGLE report, which is different from the many existing reports that all work fine);

  • debug execution from reports, so I have one process for executing the report, and the other is just the output of the results (but not to say when the report completed without additional work);

  • wait until Microsoft sees the point and allows the execution of stored procedures from UDF.

Any other ideas?

Edit May 3, 2013, here is a (very) simple example of how it hangs together:

 --Data to be reported CREATE TABLE DataTable ( MemberId INT, ProductId INT, ProductSize VARCHAR(50), Imported INT, [Weight] NUMERIC(19,2)); INSERT INTO DataTable VALUES (1, 1, 'Large', 0, 5.4); INSERT INTO DataTable VALUES (1, 2, 'Large', 1, 6.2); INSERT INTO DataTable VALUES (1, 3, 'Medium', 0, 2.3); INSERT INTO DataTable VALUES (1, 4, 'Small', 1, 1.9); INSERT INTO DataTable VALUES (1, 5, 'Small', 0, 0.7); INSERT INTO DataTable VALUES (1, 6, 'Small', 1, 1.2); --Report Headers CREATE TABLE ReportsTable ( ReportHandle INT, ReportName VARCHAR(50)); INSERT INTO ReportsTable VALUES (1, 'Large Products'); INSERT INTO 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 INTO ReportsDetail VALUES (1, 1, 'ProductSize', 'VARCHAR', '=', 'Large'); INSERT INTO ReportsDetail VALUES (2, 1, 'Imported', 'INT', '=', '1'); INSERT INTO ReportsDetail VALUES (2, 1, 'ProductSize', 'VARCHAR', '=', 'Small'); GO CREATE FUNCTION GenerateReportSQL ( @ReportHandle INT) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @SQL VARCHAR(MAX); SELECT @SQL = 'SELECT SUM([Weight]) FROM DataTable WHERE 1=1 '; DECLARE @Filters TABLE ( FilterIndex INT, DatabaseColumn VARCHAR(50), DataType VARCHAR(50), Operator VARCHAR(3), FilterValue VARCHAR(50)); INSERT INTO @Filters SELECT ROW_NUMBER() OVER (ORDER BY DatabaseColumn), DatabaseColumn, DataType, Operator, FilterValue FROM ReportsDetail WHERE ReportHandle = @ReportHandle; DECLARE @FilterIndex INT = NULL; SELECT TOP 1 @FilterIndex = FilterIndex FROM @Filters; WHILE @FilterIndex IS NOT NULL BEGIN SELECT TOP 1 @SQL = @SQL + ' AND ' + DatabaseColumn + ' ' + Operator + ' ' + CASE WHEN DataType = 'VARCHAR' THEN '''' ELSE '' END + FilterValue + CASE WHEN DataType = 'VARCHAR' THEN '''' ELSE '' END FROM @Filters WHERE FilterIndex = @FilterIndex; DELETE FROM @Filters WHERE FilterIndex = @FilterIndex; SELECT @FilterIndex = NULL; SELECT TOP 1 @FilterIndex = FilterIndex FROM @Filters; END; RETURN @SQL; END; GO CREATE PROCEDURE ExecuteReport ( @ReportHandle INT) AS BEGIN --Get the 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 I really want CREATE FUNCTION RunReport ( @ReportHandle INT) RETURNS @Results TABLE ([Weight] NUMERIC(19,2)) AS BEGIN INSERT INTO @Results EXECUTE ExecuteReport @ReportHandle; RETURN; END; --Invalid use of a side-effecting operator 'INSERT EXEC' within a function
+4
source share
2 answers

If I were in your situation, I would not try to hack something. I would set such objects as follows:

 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 ) AS INSERT [dbo].[ReportResult] SELECT @ReportID, 1, N'StackOverflow' END CREATE FUNCTION [dbo].[udf_RetrieveReportCollectionResults] ( @ReportCollectionID int ) RETURNS @Results TABLE ([ReportID], [LocationCode], [LocationName]) AS BEGIN SELECT * FROM [dbo].[ReportResult] rr JOIN [dbo].[ReportCollection] rc ON rr.ReportID = rc.ReportID WHERE rc.ReportCollectionID = @ReportCollectionID END

And use them as follows:

 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 * FROM [dbo].[udf_RetrieveReportCollectionResults](1)

Each time you run reports, create a new collection. Your application should start all reports and consolidate the results after that.

-

If you really want to call the stored procedure from udf (please do not do this), do a search in xp_cmdshell.

+2
source

If you really want this to work as a function, the worst way would be to integrate the CLR.

You don’t need to repeat everything - just write a CLR wrapper that calls the stored procedure and returns the stored procs result set as your own.

This way all your current SQL development is not affected.

0
source

More articles:


All Articles