0

below is my tablar function. I am trying to pass a mocked parameter to the my function parameter

ALTER FUNCTION [dbo].[GetContractBudgetDetail] ( @ContractId INT )
RETURNS TABLE
AS
RETURN
    (   
         SELECT     
                CBS.SdId AS Id,
                CBS.ServiceName,
                CBS.ServiceType,
                CBS.BudgetAmount AS OverallServiceBudgetAmount,
                BS.TotalPriorServicePayments AS OverallPaymentAmount,
                SUM(ISNULL(cbs.BudgetAmount,0) - ISNULL(BS.TotalPriorServicePayments,0)) as ServiceAvailable,
                ISNULL(CAST(CASE WHEN CRT.Approved ='Y' THEN 1 ELSE 0 END AS bit ),0) AS IsContractApproved, --1
                ISNULL(CAST(CASE WHEN PC.SD_id IS NOT NULL THEN 1 ELSE 0 END AS bit ),0) AS IsProviderVisible,
                ISNULL(CAST(CASE WHEN BP.BudgetParentId IS NOT NULL THEN 1 ELSE 0 END AS bit),0) AS IsBudgetVisible,
                @ContractId AS contractId --2: as per model             

            INNER JOIN Contract CRT ON CRT.Id = @ContractId--Not necessary to inclue this for all but need bcz of model--1
            LEFT JOIN GetPaymentsforBudgetedServicesbyContractId(@ContractId) BS ON CBS.SdId = BS.BudgetServiceDetail_Id
            LEFT JOIN (
                        SELECT  DISTINCT JRPS.SD_id
                        FROM    JuncContractResourceContractors JCRC
                                LEFT JOIN JuncResourceProviderServiceDetail JRPS ON JCRC.Id = JRPS.JCRC_Id
                        WHERE   JCRC.Contract_Id=@ContractId
                      ) PC ON CBS.SdId = PC.SD_id
            LEFT JOIN   (
                        SELECT  DISTINCT FSD.BudgetParentId
                        FROM    JuncContractServiceDetail JCSD
                                INNER JOIN fServiceDetail FSD ON FSD.Id = JCSD.SD_Id
                        WHERE   JCSD.Contract_Id = @ContractId
                        ) BP ON BP.BudgetParentId = CBS.SdId
    GROUP BY    CBS.SdId, CBS.BudgetAmount, 
                BS.BudgetServiceDetail_Id, BS.TotalPriorServicePayments, 
                CBS.ServiceName, CBS.ServiceType,
                CRT.Approved, --1,
                PC.SD_id,
                BP.BudgetParentId
    );

I want to declare a parameter and pass to the function parameter like this

declare @conId int;
set @conId = @ContractId;

Can some one advise how to do this ?

bajivali shaik
  • 81
  • 2
  • 13
  • Why do you think you want to do that? It will turn your inline table valued function to a multi-statement table valued function which will cripple the performance. And for what benefit? Do you think you are experiencing parameter sniffing? Let's focus on the problem you are facing, not the issue with the solution you are trying to implement. – Sean Lange Jul 16 '18 at 18:16
  • I don't even understand what it is you say you want to do. This question makes no sense to me. – Tab Alleman Jul 16 '18 at 18:17
  • Yes i want to do parameter sniffing as this query some times have performance issue. – bajivali shaik Jul 16 '18 at 18:20
  • @bajivalishaik you can't. – Eric Jul 16 '18 at 18:31
  • OK then let's fix the performance issue, not add variables and ensure the performance issue is always there. I see at least one more table valued function in here already. – Sean Lange Jul 16 '18 at 18:32
  • @Eric sure you can...but just because you can doesn't mean you should. ;) It would require some changes which are likely going to make the performance even worse, but you can declare and use scalar variables in a table valued function. – Sean Lange Jul 16 '18 at 18:33
  • @SeanLange I never know that inline function allow variable declaration. I thought it can only consist of one statement, which is immediately returned. – Eric Jul 16 '18 at 18:35
  • @SeanLange : yes the perforamce issue is there. when i pass parameter value directly i see it returns in 3 secs. but when i do sniffing it retuns in 1 sec. – bajivali shaik Jul 16 '18 at 18:49
  • @Eric yes it is known as a multi-line table valued function and the performance is in the toilet. – Sean Lange Jul 16 '18 at 18:52
  • What do you mean by "when I do sniffing"? How about an execution plan for both the good and bad? – Sean Lange Jul 16 '18 at 18:53
  • @SeanLange : i mean where ever there is contractId in the query body if i replace with conId and declare ConId int; set conId = ContractId; then the performance improves. – bajivali shaik Jul 16 '18 at 19:58
  • I can't see your screen. Can you share the code that works better? But it sounds like you are doing what your question is asking which kind of makes the question silly. – Sean Lange Jul 16 '18 at 20:00
  • 1
    Possible duplicate of [Multi-statement Table Valued Function vs Inline Table Valued Function](https://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function) – Ahmed Yousif Jul 23 '18 at 08:30
  • I think you need to read more about Multi-statement Table Valued Function vs Inline Table Valued Function – Ahmed Yousif Jul 23 '18 at 08:31
  • Thank you @Ahmed Yousif. https://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function – bajivali shaik Jul 25 '18 at 17:50

0 Answers0