• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Convert Compex Stored Procedure to UDF

When I try to convert this entire stored proc to a UDF, I get errors...it's not liking the params or something.


I need to know how to transform this (a little hand holding from ground up) on how to transform this code into a UDF so I can reuse
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there are 2 things:

* getdate()
   you cannot use getdate() inside the function, you have to pass that value are argument

* DROP /CREATE TABLE #CurrentMonthCollections_IL_FeeBased

  you cannot use temp tables in functions, you can however use table variables instead (you can remove the DROP TABLE entirely):

   DECLARE @CurrentMonthCollecitons_IL_FeeBased TABLE ( ..< field definitions go here > .. )

Aneesh RetnakaranDatabase AdministratorCommented:
1 GetDate () we can pass thru as an argument, that we can handle
2. You can't call delete inside a function to delete the contents of tables
3. You are not supposed to call extended procedures
4. As  Angel memntioned, you can't DROP /CREATE TABLE

Only these operations are permitted inside a function
The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now