Calling UDF from a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calling UDF from a stored procedure

Author

Topic

cwhite85
Starting Member

6 Posts

Posted - 2003-11-03 : 13:01:47

I have a user defined function called dbo.PersonCurrentStatus that takes in an integer as a parameter and returns a table with 2 columns.

I am calling this function from a stored procedure and am trying to send it the user id of the current record:

CREATE PROCEDURE dbo.ACCREDIT_selUsers
as

SELECT
Person_ID,
Person_Name,
Status = (SELECT CurrentStatus FROM dbo.PersonCurrentStatus(aperson.Person_ID))
FROM
Person aperson

GO

However I keep getting an error from the aperson.Person_ID being in the parameter area of the UDF I am calling.

How can I fix this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-03 : 13:17:53

Rememeber -- the "F" in UDF stands for function. Unless you are returning a table datatype, you use it just like any other function:

SELECT
Person_ID,
Person_Name,
Status = dbo.PersonCurrentStatus(aperson.Person_ID)
FROM
Person aperson

but I would recommend using joins if possible in this case. I use UDF's for calculations and such, but never for looking up values.

If you are returning a table datatype, then my question is: why? if you are returning only 1 value, return that value and not a table.

- Jeff

cwhite85
Starting Member

6 Posts

Posted - 2003-11-03 : 14:05:39

I have to return a table because I have to include the date in the select clause so I can sort by the date (this gets me the most current date first).

Is there any easier way to do this? I have to find the current status in many other stored procedures and just didnt want to have to re-write all the code.

CREATE FUNCTION dbo.PersonCurrentStatus(@PersonID int)
RETURNS @CurrentStatusTable table
(
CurrentStatus varchar(50),
CurrentStatusDate datetime
)
AS
BEGIN
INSERT @CurrentStatusTable
SELECT
TOP 1
StatusList_Lit,
PersonStatus_Date
FROM
ACCREDIT_PersonStatus astatus
INNER JOIN ACCREDIT_StatusList alist
ON astatus.StatusList_ID = alist.StatusList_ID
WHERE
ACCREDIT_astatus.Person_ID = @PersonID
ORDER BY
PersonStatus_Date DESC

RETURN
END

skillile
Posting Yak Master

208 Posts

Posted - 2003-11-04 : 12:17:23

show the rest of your sproc.
I agree a join will be more efficient.

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources