Calling a User-defined Scalar function within an Execute Statement

Question

Possible Duplicate:
Cast integer and concatenate to varchar in TSQL

I have created two scalar functions that each return uniqueidentifer types. I need to call these procedures in an execute statement but I seem to have syntax wrong.

Exec spModifyProductPropertyValue 
     @PAVID, 
     fnGetPropertyIDbyLabel(@Label7, @USPID, 0, 1), 
     @ProductID, 
     @Value7, 
     fnGetPINID(@7PIN), 
     0, 
     @counter out

What I notice is that when I call the function separately and then pass use the output from theprevious call in the call above, like so

Declare
      @PropertyID as uniqueidentifier = null

Select @PropertyID = fnGetPropertyIDbyLabel(@Label7, @USPID, 0, 1)

Exec spModifyProductPropertyValue 
         @PAVID, 
         @PropertyID, 
         @ProductID, 
         @Value7, 
         fnGetPINID(@7PIN), 
         0, 
         @counter out

The compiler does not complain much. Problem with this approach is that I end up creating so many of these temp variables(almost 50), something I want to try to avoid. I would appreciate any help in getting this correct please.

Read this: http://stackoverflow.com/questions/5967035/using-function-as-a-parameter-when-executing-a-stored-procedure. It's not possible. — prashanth, Sep 21 '12 at 18:11

score 0 · Answer 1 · answered Sep 21 '12 at 17:44

The syntax for UDF is

expression... function_name(value_1, value_2, ... value_n)...

So, you can only pass values, NOT expressions.

since using the function is an expression, inside the calling of another function

     Exec spModifyProductPropertyValue 
     @PAVID, 
     dbo.fnGetPropertyIDbyLabel(@Label7, @USPID, 0, 1) as GetPropertyID,  --Expression
     (..)

is not possible.

So, yes, you will have to create variables. But if all are int, maybe you could create one and using it in each use of function. Or modify your function to do in only one that thing that two functions do.

score -1 · Answer 2 · edited May 23 '17 at 12:21

-1

You need the owner prefix:

Exec spModifyProductPropertyValue 
     @PAVID, 
     dbo.fnGetPropertyIDbyLabel(@Label7, @USPID, 0, 1) as GetPropertyID, 
     @ProductID, 
     @Value7, 
     dbo.fnGetPINID(@7PIN) as GetPINID, 
     0, 
     @counter out

Awnser was here: SQL User Defined Function Within Select

edited May 23 '17 at 12:21

Community

1
1

answered Sep 21 '12 at 16:45

Turque

708
7
18

That results in an incorrect syntax error still. – Kobojunkie Sep 21 '12 at 16:56

Calling a User-defined Scalar function within an Execute Statement

2 Answers2