sqlcurious
asked on
unable to return value when executing the function
Hi experts, for the below function I am unable to return values even after passing valid parameter values, not undestanding why
CREATEFUNCTION [dbo].[UDF_GetInvoiceAmt]
(
@ProjectNo VARCHAR(100),
@FALocation VARCHAR(100)
--@ProgramType VARCHAR(100)
--@Listed0719 VARCHAR(10)
)
RETURNS MONEY
AS
BEGIN
-- Declare the return variable here
DECLARE @Amt MONEY
SET @Amt = 0
SELECT @Amt =SUM(ISNULL(I.[Invoice Line Amount],0))
FROM DBO.[InvoiceLineItems] (NOLOCK) I
WHERE ((I.[Project Number] LIKE '2%' AND I.[Project Number] = @ProjectNo AND I.FAID = @FALocation)
OR (I.[Project Number] LIKE '3%' AND I.[Project Number] = @ProjectNo))
GROUP BY [Project Number],FAID
RETURN @Amt
END
GO
CREATEFUNCTION [dbo].[UDF_GetInvoiceAmt]
(
@ProjectNo VARCHAR(100),
@FALocation VARCHAR(100)
--@ProgramType VARCHAR(100)
--@Listed0719 VARCHAR(10)
)
RETURNS MONEY
AS
BEGIN
-- Declare the return variable here
DECLARE @Amt MONEY
SET @Amt = 0
SELECT @Amt =SUM(ISNULL(I.[Invoice Line Amount],0))
FROM DBO.[InvoiceLineItems] (NOLOCK) I
WHERE ((I.[Project Number] LIKE '2%' AND I.[Project Number] = @ProjectNo AND I.FAID = @FALocation)
OR (I.[Project Number] LIKE '3%' AND I.[Project Number] = @ProjectNo))
GROUP BY [Project Number],FAID
RETURN @Amt
END
GO
you are not putting any values in FALocation and ProjectNo?
ASKER
when I am executing it I am giving the values
exec [dbo].[UDF_GetInvoiceAmt] '310739', '10035848'
Am I doing it wrong?
exec [dbo].[UDF_GetInvoiceAmt] '310739', '10035848'
Am I doing it wrong?
ASKER
when I run just the query:
SELECT SUM(ISNULL(I.[Invoice Line Amount],0))
FROM DBO.[InvoiceLineItems] (NOLOCK) I
WHERE ((I.[Project Number] LIKE '2%' AND I.[Project Number] = @ProjectNo AND I.FAID = @FALocation)
OR (I.[Project Number] LIKE '3%' AND I.[Project Number] = @ProjectNo))
GROUP BY [Project Number],FAID
with @ProjectNo ='310739', @FALocation= '10035848', I get the result but not when executing the function
SELECT SUM(ISNULL(I.[Invoice Line Amount],0))
FROM DBO.[InvoiceLineItems] (NOLOCK) I
WHERE ((I.[Project Number] LIKE '2%' AND I.[Project Number] = @ProjectNo AND I.FAID = @FALocation)
OR (I.[Project Number] LIKE '3%' AND I.[Project Number] = @ProjectNo))
GROUP BY [Project Number],FAID
with @ProjectNo ='310739', @FALocation= '10035848', I get the result but not when executing the function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suggest to check data type in the InvoiceLineItems table and make sure the parameters passed match the data type and the values in the table. You use VARCHAR(100) in the function and SQL implicit conversion is good up to one point...
I think the easy way to see if the issue it's due to data converions to try do a LIKE instead of the = and maybe only thing that buggers the = is leading/trailing spaces in your table columns easy to fix by LTRIM/RTRIM
I think the easy way to see if the issue it's due to data converions to try do a LIKE instead of the = and maybe only thing that buggers the = is leading/trailing spaces in your table columns easy to fix by LTRIM/RTRIM
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
try this
CREATEFUNCTION [dbo].[UDF_GetInvoiceAmt]
(
@ProjectNo VARCHAR(100),
@FALocation VARCHAR(100)
--@ProgramType VARCHAR(100)
--@Listed0719 VARCHAR(10)
)
RETURNS MONEY
AS
BEGIN
-- Declare the return variable here
DECLARE @Amt MONEY
if @projectno like '2%' or @projectno like '3%'
begin
SELECT @Amt =SUM(case when (@ProjectNo LIKE '2%' AND I.FAID = @FALocation )
or @ProjectNo LIKE '3%'
then I.[Invoice Line Amount]
end)
FROM DBO.[InvoiceLineItems] (NOLOCK) I
WHERE I.[Project Number] = @ProjectNo
end
RETURN coalesce(@Amt,0)
END
GO