SQL User Defined Functions

Question

I am currently working with User Defined Functions and having a hard time grasping the concept around how the function accepts one parameter and returns another one.

I have a table that has Employee ID and Log IDs. The UDF should accepts the EmployeeID and returns UserLoginID. The UserLoginID is the last part of the LogID column after the '\'. I need to return the entire column from the table.

Table Example

EmployeeID   LoginId
1    database\userid1
2    database\userid2
3    database\userid3

UDF query 1:

CREATE FUNCTION dbo.fx_IDtest]
(@EmployeeID int)

RETURNS nvarchar(50)
AS
BEGIN
DECLARE @UserID nvarchar(50)
SET @UserID = (SELECT SUBSTRING(LoginID,CHARINDEX('\',LoginID)+1, LEN(LoginID)) 
            FROM HumanResources.Employee
            WHERE EmployeeID = @EmployeeID
            )
RETURN @UserID
END

UDF query 2:

CREATE FUNCTION [dbo].[fx_IDtest2] 
(@EmployeeID int)

RETURNS table
AS
RETURN
(SELECT SUBSTRING(LoginID,CHARINDEX('\',LoginID)+1, LEN(LoginID)) as USERID
            FROM HumanResources.Employee
            WHERE EmployeeID = @EmployeeID
            )

What behaviour are you seeing versus what you're expecting?

Dave C
– Dave C

2014-06-20 17:56:19 +00:00
Commented Jun 20, 2014 at 17:56 — Dave C
– Dave C, Commented Jun 20, 2014 at 17:56
Also, how are you calling the function?

Dave C
– Dave C

2014-06-20 18:02:11 +00:00
Commented Jun 20, 2014 at 18:02 — Dave C
– Dave C, Commented Jun 20, 2014 at 18:02

Dave C · Accepted Answer · 2014-06-20 18:13:26Z

1

FYI... Your first function is fine, and does exactly what I believe you want... I think perhaps you're not calling the function correctly.

I tested it as follows...

/*
CREATE TABLE testDATA (EmployeeID int, LoginID nvarchar(50))

insert into testDATA
select 1,    'database\userid1' union
select 2,    'database\userid2' union
select 3,    'database\userid3'

CREATE FUNCTION dbo.fx_IDtest
(@EmployeeID int)

RETURNS nvarchar(50)
AS
BEGIN
DECLARE @UserID nvarchar(50)
SET @UserID = (SELECT SUBSTRING(LoginID,CHARINDEX('\',LoginID)+1, LEN(LoginID)) 
            FROM testDATA
            WHERE EmployeeID = @EmployeeID
            )
RETURN @UserID
END
*/

SELECT *, dbo.fx_IDtest(EmployeeID)
FROM testDATA
WHERE EmployeeID = 1

SELECT dbo.fx_IDtest(1)

Both select statements, give me userid1 as a return.

Notice, that you must provide the schema for UDF's in your SELECT, this is often overlooked. You cannot do SELECT fx_IDtest(1) instead you must do SELECT dbo.fx_IDtest(1)

answered Jun 20, 2014 at 18:13

Dave C

7,4821 gold badge21 silver badges30 bronze badges

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

1 Comment

user3745347 Over a year ago

JiggsJedi -- Thank you for your feedback. I had been toiling over this for days.

Collectives™ on Stack Overflow

SQL User Defined Functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related