asked on

Creating a user defined function

I am lost in trying to create a user defined function. I want to understand what am doing
output that I currently have

LoginID (adventure-works\alan0      ) UserLogintID (alan0) from dbo.HumanResources.Employee

1.       Create a UDF that accepts EmployeeID (2012: BusinessEntityID) and returns UserLoginID.
                  The UserLoginID is the last part of the LoginID column. It’s only the part that comes after the \
                  select LoginId
                  ,SUBSTRING(LoginID,CHARINDEX('\', LoginID,0) + 1, len(LoginId)) AS UserLoginID
                  from [HumanResources].[Employee]

--parameter to be used in the function UserLoginID we declare and set the parameter declare @UserLoginID, set @UserLoginID int (data type) , @EmployeeID int

Syntax - Scalar
CREATE FUNCTION fn_Business_Login (@UserLoginID) (@EmplyoeeID)
RETURN INT
AS
BEGIN

CODE SELECT LoginID
FROM dbo.HumanResources.Employee
WHERE

RETURN INT

END

Fred

ASKER

small clarification (BussinessID=LoginID) the 2012 was not meant to be there. The function takes a Business ID and returns the UserLoginID

Scott Pletcher

Hard to tell exactly what you're passing in and/or getting back, but this is the general structure you need:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.fn_Business_Login (
@BusinessEntityID int
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT UserLoginID
FROM dbo.HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntityID
)
/*end of func*/
END
GO

Fred

ASKER

Thank you Scott I was passing on a Business Id to return UserID string from the LoginID

John_Vidmar

I suspect dbo.HumanResources.Employee table has a field called LoginID, which contains domain\username but the function is to strip away the domain and just return username:

CREATE FUNCTION dbo.fn_Business_Login (
@BusinessEntityID int
)
RETURNS INT
AS
BEGIN
RETURN
(
SELECT SUBSTRING(LoginID,CHARINDEX('\', LoginID,0) + 1, len(LoginId))
FROM dbo.HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntityID
)
END

Scott Pletcher

I'm a bit confused.

Shouldn't that mean the return value is varchar and not int?

An int value would strongly imply it's an id of some type that is being returned.

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred

ASKER

yes this worked and thanks. My biggest Problem is how to make every thing simple. I tend to complicate my thinking and end up lost.