I have a UDF function and I found some tips to get better performance:
I tried them all but I cannot get 'WITH SCHEMABINDING' to work. Error: 'Cannot specify a sql CLR type in a Schema-bound object or a constraint expression'
Why does this mean ?
My code
ALTER FUNCTION [SIF_get_SalesAmountDosMain1]
(
-- Add the parameters for the function here
@DossierCode T_Code_Dossier
)
RETURNS T_Fin_Amount
--WITH RETURNS NULL ON NULL INPUT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @DetailCode T_Code_Detail
, @DetailSubCode T_Code_DetailSub
, @TotalSalesAmount T_Fin_Amount
, @SalesAmount T_Fin_Amount
Select @TotalSalesAmount = 0
DECLARE
Cur_DosMain CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT DetailCode
, DetailSubCode
FROM dbo.T_DossierDetail
Where DossierCode = @DossierCode
FOR READ ONLY
OPEN Cur_DosMain
FETCH Cur_DosMain INTO @DetailCode, @DetailSubCode
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT dd.DetailCode,dd.DetailSubCode
FROM dbo.T_DossierDetail dd
Inner Join dbo.T_Part p on p.PartCode = dd.PartCode
Where dd.DossierCode = @DossierCode
Select @TotalSalesAmount = @TotalSalesAmount + IsNull(@SalesAmount, 0)
FETCH Cur_DosMain INTO @DetailCode, @DetailSubCode
End
CLOSE Cur_DosMain
DEALLOCATE Cur_DosMain
-- Return the result of the function
RETURN @TotalSalesAmount
END
GO
Found it:
I was using types like T_Code_detail and T_Fin_Amount
changing them in nchar(3) and numeric(14,2) did the trick.