Insert Update Delete with function in SQL Server

Question

Can we perform Insert Update Delete statement with SQL Server Functions  I have tried with but SQL Server error is occured   Error   Invalid use of side-effecting or time-dependent operator in  DELETE  within a function    AnyBody have any Idea why we can not use Insert Update Delete statements with SQL Server functions   Waiting for your good idea s

User · Answer

Functions have only READ-ONLY Database Access  If DML operations would be allowed in functions then function would be prety similar to stored Procedure

User · Answer

Yes  you can   However  it requires SQL CLR with EXTERNAL ACCESS or UNSAFE permission and specifying a connection string  This is obviously not recommended   For example  using Eval SQL NET  a SQL CLR which allow to add C  syntax in SQL   CREATE FUNCTION  dbo   fn modify table state               conn VARCHAR 8000           sql VARCHAR 8000        RETURNS INT AS     BEGIN         RETURN SQLNET  New   using var connection   new SqlConnection conn         connection Open         using var command   new SqlCommand sql  connection                 return command ExecuteNonQuery               ValueString  conn    conn  ValueString  sql    sql  EvalReadAccessInt        END      GO  DECLARE  conn VARCHAR 8000     Data Source XPS8700 Initial Catalog SqlServerEval Debug Integrated Security True  DECLARE  sql VARCHAR 8000     UPDATE  Table 1  SET Value   -1 WHERE Name     zzz     DECLARE  rowAffecteds INT    dbo fn modify table state  conn   sql    Documentation  Modify table state within a SQL Function  Disclaimer  I m the owner of the project Eval SQL NET

User · Answer

No  you cannot   From SQL Server Books Online      User-defined functions cannot be used   to perform actions that modify the   database state    Ref

User · Answer

You can t update tables from a function like you would a stored procedure  but you CAN update table variables   So for example  you can t do this in your function   create table MyTable       ID int      column1 varchar 100    update  MyTable  set column1  My value    but you can do   declare  myTable table       ID int      column1 varchar 100     Update  myTable set column1  My value

User · Answer

No  you can not do Insert Update Delete    Functions only work with select statements  And it has only READ-ONLY Database Access   In addition    Functions compile every time  Functions must return a value or result  Functions only work with input parameters  Try and catch statements are not used in functions

User · Answer

Functions in SQL Server  as in mathematics  can not be used to modify the database  They are intended to be read only and can help developer to implement command-query separation  In other words  asking a question should not change the answer  When your program needs to modify the database use a stored procedure instead

User · Answer

Just another alternative using sp executesql  tested only in SQL 2016   As previous posts noticed  atomicity must be handled elsewhere   CREATE FUNCTION  dbo   fn get service version checksum2         ServiceId INT   RETURNS INT AS BEGIN DECLARE  Checksum INT  SELECT  Checksum   dbo fn get service version  ServiceId   DECLARE  LatestVersion INT    SELECT MAX ServiceVersion  FROM  ServiceVersion  WHERE ServiceId    ServiceId   -- Check whether the current version already exists and that it s the latest version  IF EXISTS SELECT TOP 1 1 FROM  ServiceVersion  WHERE ServiceId    ServiceId AND  Checksum     Checksum AND ServiceVersion    LatestVersion      RETURN  LatestVersion  -- Insert the new version to the table  EXEC sp executesql N  INSERT INTO  ServiceVersion   ServiceId  ServiceVersion   Checksum    Timestamp   VALUES   ServiceId   LatestVersion   1   Checksum  GETUTCDATE       N  ServiceId INT   NULL   LatestVersion INT   NULL   Checksum INT   NULL    ServiceId    ServiceId   LatestVersion    LatestVersion   Checksum    Checksum   RETURN  LatestVersion   1  END

User · Answer

Functions are not meant to be used that way  if you wish to perform data change you can just create a Stored Proc for that

User · Answer

We can t say that it is possible of not their is some other way exist to perform update operation in user-defined Function  Directly DML is not possible in UDF it is for sure   Below Query is working perfectly   create table testTbl   id int identity 1 1  Not null  name nvarchar 100    GO  insert into testTbl values  ajay     amit     akhil   Go  create function tblValued   returns Table as return  select   from testTbl where id   1  Go  update tblValued   set name   ajay sharma  where id   1 Go  select   from testTbl  Go

User · Answer

Yes  you can     Disclaimer  This is not a solution  it is more of a hack to test out something  User-defined functions cannot be used to perform actions that modify the database state   I found one way to make INSERT  UPDATE or DELETE in function using xp cmdshell   So you need just to replace the code inside  sql variable   CREATE FUNCTION  dbo    tmp func   orderID NVARCHAR 50   RETURNS INT AS BEGIN DECLARE  sql varchar 4000    cmd varchar 4000  SELECT  sql    INSERT INTO  ord  ord Code  VALUES         orderID          SELECT  cmd    sqlcmd -S       servername                   -d     db name       -Q       sql       EXEC master  xp cmdshell  cmd   no output  RETURN 1 END

User · Answer

CREATE FUNCTION dbo UdfGetProductsScrapStatus    ScrapComLevel INT    RETURNS  ResultTable TABLE    ProductName VARCHAR 50   ScrapQty FLOAT  ScrapReasonDef VARCHAR 100   ScrapStatus VARCHAR 50    AS BEGIN         INSERT INTO  ResultTable             SELECT PR Name  SUM  ScrappedQty    SC Name  NULL                 FROM  Production   WorkOrder  AS WO                         INNER JOIN                          Production Product AS PR                         ON Pr ProductID   WO ProductID                         INNER JOIN Production ScrapReason AS SC                         ON SC ScrapReasonID   WO ScrapReasonID                 WHERE WO ScrapReasonID IS NOT NULL                 GROUP BY PR Name  SC Name UPDATE  ResultTable             SET ScrapStatus                CASE WHEN ScrapQty  gt   ScrapComLevel THEN  Critical              ELSE  Normal              END          RETURN END

User · Answer

You can have a table variable as a return type and then update or insert on a table based on that output  In other words  you can set the variable output as the original table  make the modifications and then do an insert to the original table from function output  It is a little hack but if you insert the  output table from the original table and then say for example  Insert into my table select   from my function  then you can achieve the result

User · Answer

if you need to run the delete insert update you could also run dynamic statements  i e    declare       v dynDelete                 NVARCHAR 500     SET  v dynDelete    DELETE some table      EXEC  v dynDelete

[sql-server] Insert/Update/Delete with function in SQL Server

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2012