0

I have a table type like this:

CREATE TYPE [dbo].[TT_CustomType] AS TABLE
(
    [Month] [int] NULL,
    [Year] [int] NULL,
    [CountryId] [int] NULL,
    [BranchId] [int] NULL
)

From a stored procedure, I am creating a variable of this type and adding values to it.

After adding values I am passing it to another stored procedure.

DECLARE @Tbl TT_CustomType;

INSERT INTO @Tbl
    SELECT Year, Month, CountryId, BranchId 
    FROM @TEMP1 DT          
    GROUP BY Year, Month, CountryId, BranchId 
    ORDER BY DT.Year ASC, DT.Month ASC;

EXEC usp_ABC @Tbl;

However in usp_ABC, I am calling a scalar valued function to fetch data from a table tbl_RawData with respect to Month, Year, CountryId and BranchId from @Tbl. I am getting 0 results.

I am getting values from the function when I pass the actual value.

To avoid parameter spoofing, I have created a temp table variable and inserted all values from @Tbl into it.

ALTER PROCEDURE [dbo].[usp_ABC] 
    @Tbl1 TT_CustomType ReadOnly
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Tbl TABLE(MONTH INT, YEAR INT, CountryId INT, BranchId INT)

    INSERT INTO @Tbl 
        SELECT * FROM @Tbl1

    -- the following query returns 0s even if there is value for matching conditions
    SELECT
        dbo.CustomFunction(month, year, countryId, BranchId, 'option1'),
        dbo.CustomFunction(month, year, countryId, BranchId, 'option2')
    FROM
        @Tbl


    -- The following query returns values. Those are the exact values of @Tbl
    SELECT
        dbo.CustomFunction(1, 2018, 910, 1980, 'option1'),
        dbo.CustomFunction(1, 2018, 910, 1980, 'option2')
    FROM 
        @Tbl
END

ALTER FUNCTION [dbo].[CustomFunction] 
    (@Year INT, 
     @Month INT, 
     @CountryId INT, 
     @BranchId INT,
     @option NVARCHAR(200))
RETURNS DECIMAL(38, 18)
AS
BEGIN
    DECLARE @ReturnValue DECIMAL(38,18);

    SELECT
        @ReturnValue = Datavaluefield 
    FROM
        Table1 
    WHERE
        month = @Month AND year = @Year 
        AND countryId = @CountryId 
        AND BranchId = @BranchId 
        AND option = @option
END

Am I missing something or parameter spoofing is still happening. Any solution or alternatives please suggest.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
captainsac
  • 2,484
  • 3
  • 27
  • 48
  • Parameter spoofing affects query plans, not the actual results. If you recieve the values separately (not as a UDT), does the function still returns 0 rows? – EzLo Mar 27 '18 at 14:41
  • No. It returned actual value. But looping caused a lot of performance issue. – captainsac Mar 27 '18 at 14:45
  • The example function you have provided “CustomFunction” does not contain a return statement. Also provide all table definitions and example data. – Edmond Quinton Mar 29 '18 at 14:12

0 Answers0