Stored Procedure to run standard SQL queries?

[Phydos]

I have a number of SQL Update, Insert and Delete queries setup which I run as a batch in Query Analyser, is there a simple way that I could create a single Stored Procedure to run these, rather than create specific Delete...etc Stored Procedures?

Regards,
Phydos

 

[osjohnm]

Hi

Stored procs are designed to store multiple sql statements. Another benefit is that a stored procedure is compiled and executed on the server so the server takes the load not the client.

here is a basic example:

create procedure dbo.sql_queries
as

-- paste your statements here

go

exec sql_queries

You can search for info on stored procs in SQL BOL.

If you want to you can paste your sql code and one of us can help you further if you run into any trouble.

Hope this helps

John

 

[tlbroadbent]

John,

SQL Code is execute on the server whether in a stored procedure or an adhoc query sent by the client. Stored procedures don't change the load on the client or server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[osjohnm]

Terry,

Thanks for your post.

From an application point of view having sql code in a procedure or having the same sql code in an asp page, the procedure would be quicker correct or am I losing the plot again?

What is quicker, sql in a procedure, object(VB etc)or asp page and uses less client resources?

I'm a bit confused as I have read different views on this.

Could you clarify this please.

John

 

[tlbroadbent]

First, I recommend using stored procedures. There are many reasons for this that are far more compelling than enhanced performance. Security, modularity, simplification, encapsulation of business rules, etc. are all good reasons to use SPs. One performance issue that favors SPs is the ability to tune stored procedures without having to recompile and redistribute an application program.

Executing a Stored Procedure may be slightly faster than an adhoc because it is pre-compiled. The time to create an adhoc SQL string on the client and send it to SQL server to be compiled and executed certainly will take a few milliseconds more than sending the SP execution query to the server. When a query takes several seconds to run, the extra compile time is insignificant. However, if a query is very complex or the query runs in a few milliseconds, the compile time can be significant.

Regardless of whether the client sends an adhoc query or executes a stored procedure, the bulk of the work will be on the server. The time and resources required to generate an adhoc query isn't much different than generating an SP call on the client. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

Phydos,

I have created stored procedures for all of my daily tasks and as John said, I have combined several commands into one stored procedure. I then run them via a Job. This was an improvement for me, since before I would have several steps in each Job, now I only have one step - the one that executes the stored procedure. It's easier to keep my jobs updated and I can copy my stored procedure to disk and and use it on another server. Stored Procedures make my life so much easier.

-SQLBill

 

[Phydos]

Thanks, that was helpful. I've now changed all my upload tasks to stored procedures.

Best regards,
Phydos