CODEWITHSUNDEEP
t-sqlsql-server-2008
JCash
JCash

Reputation: 3

MS SQL User Defined function

this function doesnt work if i use it in Sum operator.It says" Operand data type nvarchar is sum operator.invalid for".What is the easisest solution for it?I use this function in many places that gives result numeric or nvarchar.Thanks for your help.

 USE [DSS]
    GO
    /****** Object:  UserDefinedFunction [dbo].[DECODE]    Script Date: 03/22/2011 09:30:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[DECODE]
    (@Val1 nvarchar(20),@Val2 nvarchar(10),@Val3 nvarchar(10),@Val4 nvarchar(10))
    RETURNS nvarchar(10)
    AS 
     BEGIN 
      RETURN 
      (
      Select case @Val1 When @Val2 then @Val3 else @Val4 end
      )
    END

Upvotes: 0

Views: 574

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Sum works on ints. Change datatype to int and it should work for you.

CREATE FUNCTION [dbo].[DECODE]
  (@Val1 int,@Val2 int,@Val3 int,@Val4 int)
RETURNS int
AS 
BEGIN 
  RETURN 
  (
    Select case @Val1 When @Val2 then @Val3 else @Val4 end
  )
END

Or cast the result to int in the sum

select sum(cast(dbo.decode('1','1','3','4') as int))

Upvotes: 1

Related Questions