User defined function (Function CurrentUser()) using T-SQL

I need to define a user defined function (Function CurrentUser()) using T-SQL.

Database name: main
Table: users
Fields: actual_step, nm1,nm2,nm3,x34 and x37. (all nvarchar).

The function needs to return type nvarchar after the following:

IF actual_step = 1 then CurrentUser=nm1
else
IF actual_step = 2 and x34=3 THEN CurrentUser=nm2
else
IF actual_step = 2 and x37=3 THEN CurrentUser=nm3
END
thehazeAsked:
Who is Participating?
 
GregTSmithCommented:
Oops... didn't notice you'd provided the table name... here is the same code with the table name included.
CREATE FUNCTION CurrentUser() 
 
RETURNS nvarchar(100) -- this size should be modified to match @currentUser
 
AS 
 
BEGIN 
 
	DECLARE @currentUser nvarchar(100) -- this size should be modified to match the largest length of nm1, nm2, nm3 fields
	SET @currentUser = (
		SELECT TOP 1 
			CASE 
				WHEN actual_step = 1 THEN nm1 
				WHEN actual_step = 2 AND x34 = 3 THEN nm2 
				WHEN actual_step = 2 AND x37 = 3 THEN nm3 
			END [CurrentUser] 
		FROM 
			users 
	)
 
	RETURN @currentUser 
 
END 
 
GO 

Open in new window

0
 
GregTSmithCommented:

CREATE FUNCTION CurrentUser() 
 
RETURNS nvarchar(100) -- this size should be modified to match @currentUser
 
AS 
 
BEGIN 
 
	DECLARE @currentUser nvarchar(100) -- this size should be modified to match the largest length of nm1, nm2, nm3 fields
	SET @currentUser = (
		SELECT TOP 1 
			CASE 
				WHEN actual_step = 1 THEN nm1 
				WHEN actual_step = 2 AND x34 = 3 THEN nm2 
				WHEN actual_step = 2 AND x37 = 3 THEN nm3 
			END [CurrentUser] 
		FROM 
			-- put your table name here 
	)
 
	RETURN @currentUser 
 
END 
 
GO 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.