Function returns more than more than 1 values used in Non group Select statement

Question

I have a column Civil status than has int datatype where values are from 0-4. 0 being single, 1 being married and so on.

I want the column to display the status representing their numbers so I made a function that tests the value and returns the equivalent string status value.

CREATE function civilStatus
(
@status int
)
RETURNS varchar(50)
AS
BEGIN
DECLARE @result_status varchar(50)

SELECT @result_status = (
SELECT CONVERT(varchar,(CASE Civil
WHEN 0 THEN 'Single'
WHEN 1 THEN 'Married'
WHEN 2 THEN 'Separated'
WHEN 3 THEN 'Divorced'
WHEN 4 THEN 'Windowed'
END))
FROM UserInfo)

RETURN @result_status

and I made a stored procedure that will display all rows in the table but replaced the Civil status with the function where the parameter is what number the Civil status holds in that row.

CREATE PROCEDURE [dbo].DisplayUsers
	
AS
	SELECT Firstname,
  Lastname,
  City,
  Country,
  ([dbo].civilStatus(Civil)) AS "Civil Status"
   FROM UserInfo

But the result is not what I expected. The function returned all the values of all rows in one object which returned the error about the subquery returning multiple values. How do I return the value for EACH row?

Answer 1

You can use this. It will work for you.

CREATE function civilStatus
(
@status int
)
RETURNS varchar(50)
AS
BEGIN

DECLARE @result_status varchar(50)

SET @result_status = 
CASE @status
WHEN 0 THEN 'Single'
WHEN 1 THEN 'Married'
WHEN 2 THEN 'Separated'
WHEN 3 THEN 'Divorced'
WHEN 4 THEN 'Windowed'
END

RETURN @result_status
End