SQL procedure vs. script - terminology

When one wants to update records in database using Data Manipulation Language, what would be right terminology to use:

1. UPDATE RECORDS IN TABLE VIA STORED PROCEDURE or
2. UPDATE RECORDS IN TABLE VIA SCRIPT (or UPDATE SCRIPT)
KPaxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Hi,

both terminology are ok, since you are updating data by using different options, i.e. it depends for what you need it.

You can write update script and run it or you can put that same script in the stored procedure and then run that procedure. If you need to call that script from various other scripts then the procedure will be best i.e. it enables you to reuse it as needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tapan PattanaikSenior EngineerCommented:
Hi Nenad Bulatovic,

For my understanding:

A Sql script like one or more similar sql statements those are written and execure to the  database, to perform some action.

But in "stored procedure" sql queries and condtions already written and stored in database,it can be activated with a simple command.

Use wise:

If DBA/developer wants to correct the data, good solution is to write a script file with UPDATE statements, and save it for later use.

If (DBA/developer/Application) wants to execute same code for updating records in table, then write stored procedure.

Note: for performance wise stored procedure is more effective than script file.
0
johnsoneSenior Oracle DBACommented:
Typically for maintenance type activities we use scripts.  They are usually one time use.  Even if they aren't the application users need no access to them so no need for a procedure.

Procedures are used for repeated activities.  Especially ones that application users need access to.

I would have to disagree that a procedure performs better than a script.

UPDATE TABLE A SET COL1 = 'b';

is much faster than:

CREATE PROCEDURE PROC1 AS
BEGIN
  UPDATE TABLE A SET COL1 = 'b';
END;
/
EXEC PROC1;

Just the process of executing the procedure involves allocating memory and context switches that simply executing a statement does not involve.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tapan PattanaikSenior EngineerCommented:
HI johnsone,

I will suggest go for script. As you mentioned above it will be used for onetime only.

Regards,
Tapan Pattanaik
0
KPaxAuthor Commented:
Actually, I just received notification from client that this might be used more times, so probably procedure would fit better.
0
johnsoneSenior Oracle DBACommented:
Honestly, maintenance, we keep in scripts.  We do have scripts that we run regularly.  Depends on what it is doing.  If it contains some sort of business logic, then procedure is probably best way to go.  Purely maintenance, we keep in scripts.  Sometimes things are actually easier in scripts too, it isn't that you can't do it in a procedure, it just becomes easier in a script.
0
Tapan PattanaikSenior EngineerCommented:
Please ignore my comment ID: 40752481.

I was mistakenly posting a wrong comment there. I mistakenly thought the author wants for maintenance type activity. Please ignore it. (ID: 40752481) and very sorry.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.