Insert query in SQL Function

Question

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

Can you show us what you have and explain in more detail what you're trying to do ?? — marc_s
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 — Robin clave
this is actualy a function which going to insert a row into the table. But im getting the error — Robin clave
clave: please do not put code samples or XML or stuff like that into comments - it's really hard to read and understand. Please update your original question by editing it and provide more information - thank you! — marc_s

Hans Keﬆing Hans Keﬆing · Accepted Answer · 2011-02-23T08:08:10

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.

Insert query in SQL Function

1 Answers