• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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
0
sqlcurious
Asked:
sqlcurious
2 Solutions
 
jerrypdCommented:
you are not putting any values in FALocation and ProjectNo?
0
 
sqlcuriousAuthor Commented:
when I am executing it I am giving the values
exec [dbo].[UDF_GetInvoiceAmt] '310739', '10035848'
Am I doing it wrong?

0
 
sqlcuriousAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Patrick MatthewsCommented:
How about:

SELECT [dbo].[UDF_GetInvoiceAmt]('310739', '10035848') AS Result
0
 
lcohanDatabase AnalystCommented:
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
0
 
lcohanDatabase AnalystCommented:
yeah...mathew is right - I didn't even looked at that, sorry you must run

SELECT [dbo].[UDF_GetInvoiceAmt] ('310739', '10035848')

and not

exec [dbo].[UDF_GetInvoiceAmt] '310739', '10035848'

for some odd reason SQL does not throw an error but just "Command(s) completed successfully." for the exec statement.
0
 
sqlcuriousAuthor Commented:
thanks
0
 
LowfatspreadCommented:
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

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now