Let's say that I have a column shown above(it might be listed in horizontal order sorry for the mistake) I have written a user-defined function and want to multiply the bonus by 3 but there are two same values in my column. I want the function to detect two workers who have the same bonus amount and print 'two workers have same bonus amount' who can help to get out of this?

Create function newfunction
(
    @bonus as integer
    , @workers as integer
)
returns varchar(25)
as
begin
    declare @equality as integer 
    declare @returnvalue as varchar(25)

    Select @equality = Bonus
    from Employeestbl
    Where Workers = @workers

    If @equality = @Bonus set @returnvalue = 'there is an error'

    return @returnvalue
end

select *, dbo.newfunction(employeestbl.bonus,employeestbl.workers)
from Employeestbl

CodePudding user response：

I want the function to detect two workers who have the same bonus amount and print 'two workers have same bonus amount

This query will find that:

IF EXISTS (SELECT Bonus, COUNT(*) FROM Table GROUP BY Bonus HAVING COUNT(*) > 1) PRINT 'two workers have same bonus amount'

want to multiply the bonus by 3

UPDATE MyTable SET Bonus = Bonus * 3

Assuming you don't want to apply the bonus if two bonuses are the same, heres a script to do that:

IF EXISTS (
    SELECT Bonus, COUNT(*) FROM Table GROUP BY Bonus HAVING COUNT(*) > 1
) 
BEGIN
    PRINT 'two workers have same bonus amount'
END
ELSE
BEGIN
    UPDATE MyTable SET Bonus = Bonus * 3
END

CodePudding user response：

set returnvalue this is a variable and must be set @returnvalue you have a syntax error in function.