CODEWITHSUNDEEP
sqlsql-servert-sql
Ryan Schlueter
Ryan Schlueter

Reputation: 2221

SQL Table Valued Function in Select Statement

SQL is not my best thing but I have been trying to optimize this stored procedure. It had multiple scalar-valued functions that I tried to change to table-valued functions because I read in many places that it's a more efficient way of doing it. And now I have them made but not real sure how to implement or if I maybe just didn't create them correctly.

This is the function I'm calling.

Alter FUNCTION [IsNotSenateActivityTableValue]
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
returns @T table(result varchar(max))
as
begin
DECLARE @result varchar(max);
    declare @countcodes int;


declare @ishousebill int;

select @ishousebill = count(billid)
from BillMaster
where BillID = @BillID and Chamber = 'H'

If (@ishousebill = 0)
begin


SELECT @countcodes = count([ActivityCode])
      FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode

if (@countcodes = 0)
begin
    set @result = 'test'
   end
  else
     begin
        set @result = 'test2'
    end
end
else
begin
    set @result = @TextToDisplay
end
RETURN 

END

And this is how I was trying to call them like this. I would prefer just being able to put them in the top but really anything that works would be good.

SELECT distinct       
      ActionDates.result as ActionDate
      ,ActivityDescriptions.result as ActivityDescription        
  FROM BillWebReporting.vwBillDetailWithSubjectIndex as vw
  left outer join [BillWebReporting].[HasHouseSummary] as HasSummary on vw.BillID = HasSummary.BillID
  outer APPLY dbo.IsNotSenateActivityDateTableValue(ActivityCode,vw.BillID,[ActionDate]) ActionDates    
  OUTER APPLY dbo.IsNotSenateActivityTableValue(ActivityCode,vw.BillID,[ActivityDescription]) as ActivityDescriptions

Upvotes: 1

Views: 29484

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146409

First of all UDFs generally are very non-performant. I am not sure about MySQL, but in Sql Server a UDF is recompiled every time (FOR EACH ROW OF OUTPUT) it is executed, except for what are called inline UDFs, which only have a single select statement, which is folded into the SQL of the outer query it is included in... and so is only compiled once.

MySQL does have inline table-valued functions, use it instead... in SQL Server, the syntax would be:

CREATE FUNCTION IsNotSenateActivityTableValue
(
@ActivityCode int,
@BillId int,
@TextToDisplay varchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
Select case 
   When y.bilCnt + z.actCnt = 0 Then 'test'
   when y.bilCnt = 0 then 'test2'
   else @TextToDisplay end result
From (Select Count(billId) bilCnt
      From BillMaster
      Where BillID = @BillID 
         And Chamber = 'H') y
    Full Join 
     (Select count([ActivityCode]) actCnt
      From [HouseCoreData].[dbo].[ActivityCode]
      Where ActivityDescription not like '%(H)%' 
         And ActivityType = 'S'
         And [ActivityCode] = @ActivityCode) z

)
GO

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Getting a count just to see if at least one row exists is very expensive. You should use EXISTS instead, which can potentially short circuit without materializing the entire count.

Here is a more efficient way using an inline table-valued function instead of a multi-statement table-valued function.

ALTER FUNCTION dbo.[IsNotSenateActivityTableValue] -- always use schema prefix!
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
RETURNS TABLE
AS
  RETURN (SELECT result = CASE WHEN EXISTS 
    (SELECT 1 FROM dbo.BillMaster 
     WHERE BillID = @BillID AND Chamber = 'H'
  ) THEN @TextToDisplay ELSE CASE WHEN EXISTS 
    (SELECT 1 FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' 
      and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode
  ) THEN 'test2' ELSE 'test' END
  END);
GO

Of course it could also just be a scalar UDF...

ALTER FUNCTION dbo.[IsNotSenateActivityScalar] -- always use schema prefix!
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @result VARCHAR(MAX);

  SELECT @result = CASE WHEN EXISTS 
    (SELECT 1 FROM dbo.BillMaster 
     WHERE BillID = @BillID AND Chamber = 'H'
  ) THEN @TextToDisplay ELSE CASE WHEN EXISTS 
    (SELECT 1 FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' 
      and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode
  ) THEN 'test2' ELSE 'test' END
  END;

  RETURN (@result);
END
GO

Upvotes: 3

geomagas
geomagas

Reputation: 3280

Table-valued functions return a table, in which, like any other table, rows have to be inserted.

Instead of doing set @result = ....., do:

INSERT INTO @T (result) VALUES ( ..... )

EDIT: As a side note, I don't really understand the reason for this function to be table-valued. You are essentially returning one value.

Upvotes: 0

Related Questions