Calling a UDF Several Times - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calling a UDF Several Times

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-29 : 15:35:35

I have a query which needs the value from a User Defined Function and that UDF is included several different times in that query.

It is always with the same parameter and therefore will return the same value.

Does the SQL server actually run that UDF repeatedly with teh same value or does it only run once retain it for the rest of the query?

Should I be creating a variable, assigning the value of that UDF to it, and then referencing the variable repeatedly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-29 : 15:40:11

It'll run it each time you call it. So just put it into a variable to avoid the unnecessary calls.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-29 : 15:41:49

Oops. Thank you Goddess.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-29 : 15:59:33

This of course begs the question then how does one assign a during an operation like this:

        DECLARE @ShipNotInv  AS NUMERIC (14,5)
SELECT

         sorels.identity_column,
                SOMAST.FSONO AS SO#,
                SOMAST.FSOREV AS REV,
                SOITEM.FPRODCL AS PROD_CLASS,
                SOMAST.FCOMPANY AS COMPANY,
                SOMAST.FCUSTNO AS CUST#,
                SORELS.FDUEDATE AS DUEDATE,
                SORELS.FORDERQTY AS ORDER_QTY,
                SOITEM.FINUMBER AS SO_ITEM#,
                SORELS.FRELEASE AS RELEASE,
                SORELS.FPARTNO AS PART#,
                SORELS.FPARTREV AS PART_REV,
                sorels.fsono,
                sorels.fenumber,
                sorels.frelease,
                ( SORELS.forderqty - SORELS.finvqty
                  - ( CASE WHEN ( ( SORELS.fshipbook + SORELS.fshipbuy
                                    + sorels.fshipmake ) - sorels.finvqty ) <= 0
                                OR COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),
                                            0) <= 0 THEN 0
                           WHEN ( SORELS.fshipbook + SORELS.fshipbuy
                                  + sorels.fshipmake ) - sorels.finvqty < COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease), 0)
                           THEN ( SORELS.fshipbook + SORELS.fshipbuy
                                  + sorels.fshipmake - sorels.finvqty )
                           ELSE COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),
                                         0)
                      END ) ) * SORELS.funetprice AS FNBOAMT
         FROM   SORELS
                JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                               AND SOITEM.FINUMBER = SORELS.FINUMBER
         WHERE  SOMAST.FSTATUS = 'OPEN'
                AND FMASTERREL = 0
                AND somast.forderdate >= CONVERT(DATETIME, '01/01/2000')

@ShipNotInv would = COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:08:30

One think you could do is to call the UDF and put the results onto a temporary table before main query. then join onto the temporary table in main query to retrive the values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources