Convert SP to Function 2

[kxramse]

I think I spent my brain power for the day; basically I'm confused after trying to convert a SP to a function.

I have a Stored Procedure that I want to convert into a Function. I basically need to pass the function 3 variables and return 1 variable. The stored procedure does it great, but it uses a temporary table and I can't seem to create a temp table in a UDF. I know I can create a table, but I don't want to return the table, I just want to build a temporary space so that I can sum its findings. I've tried calling the SP from the Function, that didn't work, I thought about a view, but then I couldn't pass my variables to it since the joins are so complex. So, any ideas how I can convert this SP to a function?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spVolumeLife]
	@ReportMonth int,
	@ReportYear int,
	@MarketClient varchar(1)
AS
BEGIN
	SET NOCOUNT ON;

CREATE TABLE #SumarizedVolumesByRlifeID
	(VolumeLifeReinsure Decimal(20,2))
INSERT #SumarizedVolumesByRlifeID(VolumeLifeReinsure)
	SELECT 
		(CASE WHEN (SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2)))) > 150000 THEN 
			SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2))) - 150000 ELSE 
				0 END) AS VolumeLifeReinsure
	FROM 
		  {oj (((((("Reports" Reports INNER JOIN "Sites" Sites ON Reports."SiteID" = Sites."ID") 
		 INNER JOIN "ReportLives" ReportLives ON Reports."ID" = ReportLives."ReportID") 
		 INNER JOIN "ReportPS" ReportPS ON ReportLives."ID" = ReportPS."rLifeID") 
		 INNER JOIN "SiteGroups" SiteGroups ON Sites."SiteGroupID" = SiteGroups."ID") 
		 INNER JOIN "ReportPSBreaks" ReportPSBreaks ON ReportPS."ID" = ReportPSBreaks."rPSID") 
		 INNER JOIN "Companies" Companies ON SiteGroups."CompanyID" = Companies."ID") 
		 INNER JOIN "ReportPlans" ReportPlans ON ReportPS."rPlanID" = ReportPlans."ID"}
	WHERE 
	  Reports."ReportMonth" = @ReportMonth
		AND Reports."ReportYear" = @ReportYear
		AND [Companies].[MarketClient]=@MarketClient
		AND (ReportPlans."PlanTypeName" LIKE 'My Plan') 
		AND Reports."Submitted" = 1 
		AND Sites."ID" NOT IN ( 142,350 ) 
	Group by ReportPS."rLifeID"
	having (SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2))) > 150000)

Select Sum(VolumeLifeReinsure) from #SumarizedVolumesByRlifeID

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

 

[gmmastros]

Yep. You can't create temp tables in a UDF. You can create table variables though. Your sproc converted to use table variables would look like this...

ALTER PROCEDURE [dbo].[spVolumeLife]
    @ReportMonth int,
    @ReportYear int,
    @MarketClient varchar(1)
AS
BEGIN
    SET NOCOUNT ON;

[!]Declare @SumarizedVolumesByRlifeID
Table	(VolumeLifeReinsure Decimal(20,2))[/!]

INSERT [!]@[/!]SumarizedVolumesByRlifeID(VolumeLifeReinsure)
    SELECT 
        (CASE WHEN (SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2)))) > 150000 THEN 
            SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2))) - 150000 ELSE 
                0 END) AS VolumeLifeReinsure
    FROM 
          {oj (((((("Reports" Reports INNER JOIN "Sites" Sites ON Reports."SiteID" = Sites."ID") 
         INNER JOIN "ReportLives" ReportLives ON Reports."ID" = ReportLives."ReportID") 
         INNER JOIN "ReportPS" ReportPS ON ReportLives."ID" = ReportPS."rLifeID") 
         INNER JOIN "SiteGroups" SiteGroups ON Sites."SiteGroupID" = SiteGroups."ID") 
         INNER JOIN "ReportPSBreaks" ReportPSBreaks ON ReportPS."ID" = ReportPSBreaks."rPSID") 
         INNER JOIN "Companies" Companies ON SiteGroups."CompanyID" = Companies."ID") 
         INNER JOIN "ReportPlans" ReportPlans ON ReportPS."rPlanID" = ReportPlans."ID"}
    WHERE 
      Reports."ReportMonth" = @ReportMonth
        AND Reports."ReportYear" = @ReportYear
        AND [Companies].[MarketClient]=@MarketClient
        AND (ReportPlans."PlanTypeName" LIKE 'My Plan') 
        AND Reports."Submitted" = 1 
        AND Sites."ID" NOT IN ( 142,350 ) 
    Group by ReportPS."rLifeID"
    having (SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2))) > 150000)

Select Sum(VolumeLifeReinsure) from [!]@[/!]SumarizedVolumesByRlifeID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

You are aware that functions are often performance killers?

In any case just use a derived table instead of a temptable.

Select  Sum(VolumeLifeReinsure) from 
	(SELECT 
        (CASE WHEN (SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2)))) > 150000 THEN 
            SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2))) - 150000 ELSE 
                0 END) AS VolumeLifeReinsure
    FROM 
          {oj (((((("Reports" Reports INNER JOIN "Sites" Sites ON Reports."SiteID" = Sites."ID") 
         INNER JOIN "ReportLives" ReportLives ON Reports."ID" = ReportLives."ReportID") 
         INNER JOIN "ReportPS" ReportPS ON ReportLives."ID" = ReportPS."rLifeID") 
         INNER JOIN "SiteGroups" SiteGroups ON Sites."SiteGroupID" = SiteGroups."ID") 
         INNER JOIN "ReportPSBreaks" ReportPSBreaks ON ReportPS."ID" = ReportPSBreaks."rPSID") 
         INNER JOIN "Companies" Companies ON SiteGroups."CompanyID" = Companies."ID") 
         INNER JOIN "ReportPlans" ReportPlans ON ReportPS."rPlanID" = ReportPlans."ID"}
    WHERE 
      Reports."ReportMonth" = @ReportMonth
        AND Reports."ReportYear" = @ReportYear
        AND [Companies].[MarketClient]=@MarketClient
        AND (ReportPlans."PlanTypeName" LIKE 'My Plan') 
        AND Reports."Submitted" = 1 
        AND Sites."ID" NOT IN ( 142,350 ) 
    Group by ReportPS."rLifeID"
    having (SUM(CAST(ReportPS.[Volume] AS Decimal(10, 2))) > 150000)) a

make sure to give your derived table an alias (a in the example) or it won't work.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

SQLSister has a good point. You don't need a temp table or a table variable. For this example, her advice is spot on.

However, please take a look at my advice also. In most cases, table variables will outperform temp tables, so it is useful to know how to change a procedure that uses temp tables in to 1 that uses table variables.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kxramse]

I can create a table in my function? I thought I couldn't alter the db from a function. I am confused. How would the function look?

 

[gmmastros]

You cannot create a table or a temp table.

You can create table variables (similar to temp tables). You can also change the query to use a derived table, which is not a REAL table either. Both techniques would work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kxramse]

I swear I tried ten ways to sum my entire select statement. I don't think I ever tried to give the select statement the alias like you suggested SQLSister. Thanks!

I also didn't understand before that I could create a table variable and not return the table variable. Thanks gmmastros!

I have my function created. Thanks everyone!