Post reply

UDFs and ByRef parameters - help

  • October 19, 2005 at 12:05 am

    #89490

    Is it possible to call a SQL Server user defined function from within a stored procedure with a ByRef parameter  just like in Visual Basic ?

    I have a UDF which performs as required and returns the intended result but I also want it to be able to alter the value of one of its parameters such that the new value goes back into the @local-variable in the parameter list in the stored proc from which the UDF was called.

    I have coded the UDF to alter the parameter value but the new value is not returned to the @local-variable in the stored proc.  VB can do it, Access can do it (VB anyway) - can T-SQL do it ?

  • October 19, 2005 at 1:02 am

    #598151

    No, it is not possible.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • October 19, 2005 at 1:03 am

    #598152

    No. Stored procedures may have output parameters but functions may not. See the following from BoL.

    A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters.

    The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

    If a function could change a value of a variable in the calling procedure, it would be a side effect, which is forbidden.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply