We help IT Professionals succeed at work.

Function is Correct, but Returns Incorrect Value when Called by SP

Medium Priority
Last Modified: 2020-02-25
A function returning a string does so correctly when calling it as stand-alone correctly returns:

EXEC EmployeeDietaryRestriction_SELECT_DietPerEmpID 184

Returns: 'Gluten-Free,Vegetrian,Vegan,' < correct

However, when called in an SP it returns a incorrect value:

EXEC @DietaryRestriction = EmployeeDietaryRestriction_SELECT_DietPerEmpID @EmployeeID --184

It Returns: '0' which is incorrect. It looks like a result code.

Why doesn't it work called from an SP? What am I missing here? Thank you for any help.
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

It looks like a proc, not a function, is being used.  And that format, EXEC @var = <proc_name>, is explicitly designed to provide the return code of a proc.

If it is a proc, you need to either have an OUTPUT parameter or INSERT the results of the proc into a table to get the return result set from the proc.

CREATE TABLE #results ( result_string varchar(8000) NULL );

INSERT INTO #results

EXEC EmployeeDietaryRestriction_SELECT_DietPerEmpID @EmployeeID

SELECT * FROM #results --should give you   'Gluten-Free,Vegetrian,Vegan,'


My bad. It was a proc. I changed it to a function and all is well. Thanks for the help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.