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
– marc_s, Commented Feb 23, 2011 at 7:55
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
– Robin clave, Commented Feb 23, 2011 at 8:01
this is actualy a function which going to insert a row into the table. But im getting the error — Robin clave
– Robin clave, Commented Feb 23, 2011 at 8:01
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
– marc_s, Commented Feb 23, 2011 at 8:10

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

14

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.

answered Feb 23, 2011 at 8:08

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert query in SQL Function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related