December 30, 2011 at 3:49 am
Hi All,
Im kinda puzzled on how i can do this, as i have only used SQL for basic things and now i want to build upon my knowledge.
What i want to do is this
Within a Stored Procedure i want to pass a parameter which is a USERID of a User to a function which will return there full name, Email Address, Network id etc
This is what i have so far
This is my Stored Proc
@spUserID int = NULL
DECLARE @UserName as varchar(max)
DECLARE @UserEmail as varchar(max)
Set @UserName = dbo.FnGetUserDetails(@spUserID)
This is my function and you can see im trying to return a two values, but im kinda puzzled how i can get the UserEmail once its been returned
USE [TaskTracker]
GO
/****** Object: UserDefinedFunction [dbo].[FnGetUserDetails] Script Date: 12/30/2011 10:44:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FnGetUserDetails]
(@UserID int) -- Add the parameters for the function here
RETURNS int
AS
BEGIN
DECLARE @UserFullName varchar(max) -- Declare the return variables here
DECLARE @UserEmail varchar(max)
Select @UserFullName = (select chrFullName from [User] where intUserID = @user-id)
Select @UserEmail = (select chrEmail from [User] where intUserID = @user-id)
RETURN @UserFullName, @UserEmail -- Return the result of the function
END
Any help will be highly appreciated.
December 30, 2011 at 4:17 am
You don't need a function to return the values you're looking for:
In your sproc simply use the select statement:
SELECT
@UserFullName = chrFullName,
@UserEmail = chrEmail
FROM[User]
WHERE intUserID = @user-id
If you want to try how to use a function, you should also get familiar with the types of functions available (scalar valued function, Inline Table-valued Function, and Multistatement Table-valued Function) and the advantages/disadvantages of each one.
The function you're looking for is a good candidate for an Inline Table-valued Function (or iTvF):
ALTER FUNCTION [dbo].[FnGetUserDetails]
(@UserID int) -- Add the parameters for the function here
RETURNS TABLE
AS
RETURN
(
SELECT
chrFullName,
chrEmail
FROM[User]
WHERE intUserID = @user-id
)
In your sproc you would then call the function:
SELECT
@UserFullName = chrFullName,
@UserEmail = chrEmail
FROM [dbo].[FnGetUserDetails] (@UserID)
Edit: as a side note: don't use reserved key words as table name, column name or variable...
December 30, 2011 at 4:38 am
Hi Lutz,
Thank you for the reply and additional information i will take a look at all different functions in great detail.
Thanks again 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply