How to call a scalar function in a stored procedure

Question

I am wacking my head over the problem with this code.

DECLARE @root hierarchyid
DECLARE @lastchild hierarchyid
SELECT @root = NodeHierarchyID FROM NodeHierarchy WHERE ID = 1
SET @lastchild = getlastchild(@root)

It says it does not recognize getlastchild function. What am I doing wrong here?

score 49 · Accepted Answer · edited Feb 23 '13 at 01:35

49

try including the schema id, as in

@lastchild = dbo.getlastchild(@root)

edited Feb 23 '13 at 01:35

Mitch Wheat

295,962
43
465
541

answered Apr 20 '10 at 16:59

SeaDrive

4,182
5
32
30

16

+1 you **CAN'T** call a user defined function in SQL Server 2008 without using the schema – KM. Apr 20 '10 at 17:20
1

but, don't forget the "SET", e.g., `SET @lastchild = ...` – STLDev Mar 11 '16 at 16:37

Adriaan Stander · Answer 2 · 2010-04-20T17:07:13.793

13

Use

set @lastchild = dbo.getlastchild(@root)

From CREATE FUNCTION

Scalar-valued functions may be invoked where scalar expressions are used, including computed columns and CHECK constraint definitions. When invoking scalar-valued functions, at minimum use the two-part name of the function.

edited Apr 20 '10 at 17:07

answered Apr 20 '10 at 17:01

Adriaan Stander

162,879
31
289
284

score 3 · Answer 3 · edited Aug 26 '14 at 16:47

3

Try:

SELECT * FROM dbo.function(@parameters)

edited Aug 26 '14 at 16:47

Simple Sandman

900
3
11
34

answered May 23 '14 at 05:15

Vijay Singh Rana

1,060
14
32

score 3 · Answer 4 · answered Apr 20 '10 at 17:00

3

Try

set @lastchild = dbo.getlastchild(@root)

answered Apr 20 '10 at 17:00

Thomas

7,933
4
37
45

How to call a scalar function in a stored procedure

4 Answers4

Linked