CODEWITHSUNDEEP
sqlsql-server-2008
Robin clave
Robin clave

Reputation: 638

Insert query in SQL Function

Can i write a insert query inside Function in SQL server 2008. If i tried, im a getting an error of Invalid use of side effecting operator 'INSERT' within the function. Please help me out. But i want it to be a function, not a stored procedure

Create function EFT_DL_FUNC_AUDTI_BATCH_START (@i_db_name varchar(20))
returns int as
begin
    insert into table_name(db_name) values (@i_db_name)
    return 0
end

Upvotes: 3

Views: 24622

Answers (1)

Hans Kesting
Hans Kesting

Reputation: 39283

Quote from here:

User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.

So you can't do an INSERT in a function.

You might want to explain WHY you don't want to use a procedure.

Upvotes: 14

Related Questions