[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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
0
thehaze
Asked:
thehaze
  • 2
1 Solution
 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now