MS SQL User Defined function

Question

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

What are you trying to achieve? Concatenate all the strings returned by function, or you actually have numbers in string form in those varchars? — Kaerber
– Kaerber, Commented Mar 22, 2011 at 7:42

Mikael Eriksson · Accepted Answer · 2011-03-22 07:42:21Z

1

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))

answered Mar 22, 2011 at 7:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

2 Comments

JCash Over a year ago

i cant change inpt parameter types because i use this function for nvarchar outputs too.I had thought second solution before wrote here.Just want to check that there s any solution easier than ur second way.Thanks for ur help

Mikael Eriksson Over a year ago

@JCash You could also add a integer version of Decode, DecodeInt and use that when you need to deal with integers.

Collectives™ on Stack Overflow

MS SQL User Defined function

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related