What Is a Stored Procedure

What is a stored procedure?

Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples.

To create a stored procedure the syntax is fairly simple:

CREATE PROCEDURE .

      

AS

     

So for example:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL injection.

Stored procedures do come with downsides, basically the maintenance associated with your basic CRUD operation. Let's say for each table you have an Insert, Update, Delete and at least one select based on the primary key, that means each table will have 4 procedures. Now take a decent size database of 400 tables, and you have 1600 procedures! And that's assuming you don't have duplicates which you probably will.

This is where using an ORM or some other method to auto generate your basic CRUD operations has a ton of merit.

What's the point of a stored procedure?

Stored procedures are code that runs on the database server.

They have a number of uses. Think: If I could run code directly on the database server, what could I use that for?

Among their many uses, stored procedures can be used to shift some of the processing load to the database server, to reduce network traffic, and to improve security.

http://en.wikipedia.org/wiki/Stored_procedure

What is the difference between a stored procedure and a view?

A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table.

A stored procedure uses parameters to do a function... whether it is updating and inserting data, or returning single values or data sets.

Creating Views and Stored Procedures - has some information from Microsoft as to when and why to use each.

Say I have two tables:

• tbl_user, with columns: user_id, user_name, user_pw
• tbl_profile, with columns: profile_id, user_id, profile_description

So, if I find myself querying from those tables A LOT... instead of doing the join in EVERY piece of SQL, I would define a view like:

CREATE VIEW vw_user_profile
AS
  SELECT A.user_id, B.profile_description
  FROM tbl_user A LEFT JOIN tbl_profile B ON A.user_id = b.user_id
GO

Thus, if I want to query profile_description by user_id in the future, all I have to do is:

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

That code could be used in a stored procedure like:

CREATE PROCEDURE dbo.getDesc
    @ID int
AS
BEGIN
    SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
END
GO

So, later on, I can call:

dbo.getDesc 25

and I will get the description for user_id 25, where the 25 is your parameter.

There is obviously a lot more detail, this is just the basic idea.

Find the stored procedure which uses a particular table in sql server

The following query maybe of help to you.

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS S
INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.TEXT LIKE '%Table_name%'
    AND O.type = 'P'

It can search if a particular word is contained in the stored procedure. If a table name is what you need to find, then it can find it from the stored procedure text. Hope it helps.

Why is sql stored procedure called a stored procedure

Why is a Stored Procedure called stored procedure

Because it is a procedure that is stored in a database.

In other languages/environments, procedures that can be executed aren't usually stored. They are compiled in either bytecode or assembler. I.e. the procedure does not exist in its original textual form anymore. The original procedure cannot be retrieved as it was when it was created in those environments (although reverse engineering can retrieve the essence of that procedure).

When you create a stored procedure in SQL Server, it is completely stored in its original full-text form, same indentation, same casing, same lines, including comments and all. You can retrieve the text with which you created the stored procedure in its entirety.

Simplified explanation about executing a stored procedure

When SQL Server wants to execute a stored procedure, it will first check the cache to see if it has been compiled already. If it finds an entry in the cache (in the form of an execution plan) it will use this entry to execute. If it doesn't find an entry it will compile the procedure into an execution plan, store it in the cache for later use, then execute it using the execution plan.

There are cases that force a stored procedure to be recompiled, e.g. when the execution plan cache is cleared (schema changes, statistics updates, ...) or when supplying commands to the compiler that force recompilation (stored procedure WITH RECOMPILE, query option OPTION(RECOMPILE), ...).

How to call external stored procedure from SQL stored procedure and handle commitment control

Try this my friend:
On SP1

BEGIN                                             
  DECLARE SQLSTATE CHAR(5) DEFAULT ' ';           
  DECLARE SQLCODE INTEGER DEFAULT 0;    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;         
    CALL MYLIB.SP2(PRINPUT, PRERR);      
      IF SQLCODE = 0 THEN                         
        COMMIT;
      ELSE 
        ROLLBACK;                          
      ENDIF;                                      
END 

Answers:

PARTA: If the Ext Stored Proc exists, the way you're calling it, it's correct.

PARTB: The set transaction scopes all the rows changed from the moment is issued, to the execution of commit or rollback. Remember this, every SQL Stored proc runs on *caller actgrp, so, you need to check if your RPG program runs on *caller too.

Finally, last time I tested, dinos still walked the earth, the commit on SQL Stored Proc scoped the changes made with a RPG program called within, but the RPG STRCMTCTL doesn't get the changes made on SQL Stored Proc called within the RPG.

Have fun!

statements-set-transaction

control-example-using-transaction-logging-file-start-application

definition-example-jobs-commitment-definitions

---

Related Topics