CODEWITHSUNDEEP
sqlsql-server
MYK
MYK

Reputation: 845

use queries from program or use stored procedure for inserting/update/delete data in database

I want to ask that which is a better way to insert/update/delete data in to database, to use queries directly from data-layer or create stored procedure for every inserting and deletion?

Upvotes: 1

Views: 338

Answers (2)

Aijaz Chauhan
Aijaz Chauhan

Reputation: 1649

i always used a Stored procedure to do simple CRUD operation

i used to do all CRUD operation within one SP, hope it will help.

For Example

create proc <procedure name>
(
  <parameterName> <datatype>=<default value if any>,
  @mode varchar(20)
)
as
begin

 if(@mode='Insert')
 begin

    --your query for insert
 end

 if(@mode='Update')
 begin

    --your query for update
 end

 if(@mode='Delete')
 begin

    --your query for delete
 end

 if(@mode='Select')
 begin

    --your query for Select
 end

end

you can have more "Mode" like 'select by id','soft delete','hard delete',etc...

Upvotes: 0

DougM
DougM

Reputation: 2888

Your question depends entirely on the type of queries you're talking about.

  • For simple CRUD operations on a single table with no database-side rules that need to be followed, direct SELECT, UPDATE, or DELETE statements are fine.

  • For more complex operations, such as when your database applies normalization and access multiple tables, a stored procedure is worth the work. (In fact, "use stored procedures only when they're worth the extra work" is a good rule.)

  • For simple CRUD for multiple rows in a single query call, a program query is the only way to go unless you want to muck about with XML.

  • (And for simple CRUD where you're going to be using a recordset-aware interface, either should work just fine.)

Upvotes: 1

Related Questions