Use if, else in function in sql query

Question

I use the below query,but throws an error:

CREATE FUNCTION getCustomerPaymentFunc (@customerCode bigint ) 
RETURNS bigint AS BEGIN
    RETURN 
    ( if(select coun from getCustomerPaymentCount (@customerCode))=0)
     select 0 as price      
      else          
        (select SUM(price) as code 
         from PaymentLog 
         where customerCode=@customerCode)
    ) END

I Use below Two but says:

select statement included within a function cannot return data to client

CREATE FUNCTION getCustomerPaymentFunc (@customerCode bigint ) 
RETURNS bigint AS BEGIN
RETURN 
(
  SELECT CASE WHEN (select coun from getCustomerPaymentCount(@customerCode))=0      
     THEN 0 ELSE 1 END as
 (select SUM(price) as code from PaymentLog 
  where customerCode=@customerCode)
     ) END

There is so much syntactical errors in you code that disallows to understand your intention. Can you explain in human language (not in SQL) your intention? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 29, 2014 at 11:44

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

It looks like your intention is to have the function return the sum of price in the PaymentLog table for a given customerCode, or return 0 if the customerCode doesn't exist. If this is correct you could do this:

CREATE FUNCTION getCustomerPaymentFunc (@customerCode bigint) RETURNS bigint AS 
BEGIN
    DECLARE @result bigint
    SET @result= (SELECT ISNULL(SUM(price), 0) FROM PaymentLog WHERE customerCode = @customerCode)
    RETURN @result
END

If thecustomerCodedoesn't exist in thePaymentLogtable theSUM()function will returnNULLand you can use either theCOALESCEor theISNULLfunction to replace the NULLvalue with 0.

See MSDN: COALESCE and ISNULL

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 29, 2014 at 12:04

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use if, else in function in sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related