SQL codes VS Store procedures

Transact-SQL
1

Hello,

Can SQL codes answer all your questions or in some cases you need store procedure no matter what.

I sometimes find that SQL codes can’t always do what you need because the SQL codes can be very complex with the date changes and when calculating for a long range of period. In this case I think the answer from the database base on its design needs programming or store procedure.

Do you agree.

2

What do you understand the following to be
SQL codes vs stored procedures?

3

SQL is just query statements to extract data out of database.

Store procedure includes SQL and programming and is usually use in routine or procedures. Store Procedure can do what SQL queries can’t.

For example, if I were to scan a list and find in the list the last update date for each item, SQL codes can’t do that.

What do you advise given my Q in the post ?

4

A stored procedure is just a way of packaging SQL in a reusable form, like a module or function in any other programming language. A stored procedure and a query are both just composed of SQL. It is purely a matter of convenience and good programming style as to which is more appropriate in any given context. A stored procedure may be a good choice when you wish to pass parameters to a set of SQL queries or be able to call it from multiple places.

5

Almost always stored procedures are the better option. There are a few considerations that argue in favor of stored procedures:

  1. Encapsulation - The client program/caller does not need to know the internals of how you have implemented the queries. They just need to know the interfaces. This means that you can change the underlying queries, tables, or whatever else without affecting the callers.

  2. Plan reuse - When you run a stored proc for the first time, it creates an execution plan and stores that in the plan cache. Next time when you invoke the same stored proc, SQL Server can reuse the execution plan, and thus avoid the usually expensive step of creating an execution plan. There are exceptions, but this works out well most of the time.

  3. Security - Microsoft recommends that clients be given only the minimum required permissions. With stored procedures you can grant the clients permission to execute the stored proc and nothing else (in most cases). This reduces the surface area making your environment more secure.

  4. All the code in one place - Related to (1), but just as important is that when you want to make changes to the database schema or queries, you wouldn't need to look through various code fragments you may have distributed to various clients and update them.

As for me I have strict rule that nobody is allowed to access my databases other than through stored procedures.

1 Like