Calling a Function from a Stored Procedure

Hello all,

I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error.

I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.
The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.

Example of tool SELECT statement:

SELECT tool_number, tool_descriptionFROM toolWHERE tool_manufacturerIN (UDFmanufacturer_SearchName(@.search_string)

This gives me an error:
'UDFmanufacturer_SearchName' is not a recognized built-in function name.

Function code (removed some wrapping code for simplicity):

SELECT manufacturer_idFROM manufacturerWHERE manufacturer_nameLIKE'%' + @.search_string +'%'

These statements both work if I run a independent query: 

SELECT *FROM UDFmanufacturer_SearchName('mol')
 
 
SELECT *FROM toolWHERE tool_manufacturerIN (SELECT *FROM UDFmanufacturer_SearchName('mol'))
This code fails:

SELECT *FROM ato_toolWHERE ato_tool_manufacturerIN (UDFmanufacturer_SearchName('mol')) 
I'm stuck.  I haven't been able to find anything that shows me where I'm going wrong.  Any thoughts or suggestions are appreciated.
 
Thanks,
Jay

You have to use a SELECT FROM function and not directly in the IN clause. Also use the prefix "dbo." for the function and see if it helps.

SELECT *FROM toolWHERE tool_manufacturerIN (SELECT *FROMdbo.UDFmanufacturer_SearchName('mol'))

|||

That was it. I thought I could use the function without needing the " SELECT * " in front.

Thanks for your help.

|||

The above post is absolutely correct.

UDFmanufacturer_SearchName is a table valued function (fancy for it returns a table/resultset). If you then think of UDFmanufacturer_SearchName('mol') as being a table, then

SELECT *
FROM ato_tool
WHERE ato_tool_manufacturerIN (UDFmanufacturer_SearchName('mol'))

isn't valid for the same reasons that:

SELECT *
FROM ato_tool
WHERE ato_tool_manufacturerIN (tool_manufacturer)

isn't valid.

So because tables aren't valid where you've placed it, it's looking for a different type of function, like one that returns a string, date, or number. It can't find one, so it says it can't find the function. Also, you should always prefix your function names with 'dbo.' or the correct schema. It may work without it in some places, but I believe syntactically it's wrong (sloppy). Don't be suprised if a service pack, hotfix, or the next version of SQL Server starts to fail on it.|||

Thanks for the explanation, it was helpful.

Hadn't thought about the schema prefix. Will keep it in mind.

Thanks.