-1

I would like to update data in a table (for sqlserver and oralce version).

I created a stored procedure as below, but i would like to convert it to SQL function, is it possible to update data within SQL function please?

CREATE PROCEDURE updatetable (@A1 INTEGER, @A2 VARCHAR(4000) )
AS
  BEGIN
        BEGIN
            UPDATE table SET column1= column1+ @A1 WHERE column2= @A2 ;
        END    
  END
satcha
  • 129
  • 1
  • 13
  • 1
    Why does this need to be a function? Why is the procedure insufficient? – Aaron Bertrand Apr 27 '22 at 16:33
  • 3
    From Oracle perspective, technically yes, but should not be done and procedure should be used, rather for DMLs. Refer - https://asktom.oracle.com/pls/apex/asktom.search?tag=function-with-dml-ddl – Pankaj Apr 27 '22 at 16:47
  • 1
    Wanting to convert to a function (and slightly guessing what you mean by a 'SQL function') suggests you want to be able to call the function as part of a query. Oracle [won't let you do that](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/coding-subprograms-and-packages.html#GUID-035E5BD6-A547-4E88-9C41-52575A4298FE) either (really gross and dangerous hacks aside *8-). – Alex Poole Apr 27 '22 at 17:01

1 Answers1

2

For SQL Server.

Simply put:

No.

A function can't change the system (really gross and dangerous hacks aside). From the documentation (emphasis mine):

Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table

If you can explain what you're trying to accomplish with a function that you can't accomplish with a stored procedure, you might be asking a question that has more than a yes/no answer, and you might get useful alternatives.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I just wanted to know if it is possible to convert it into a function, but it seems that it is not possible so I keep the stored procedure.Thanks a lot for details – satcha Apr 27 '22 at 17:08