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.