Scribd Logo
Upload

Welcome to Scribd!

  • 400 views

    Stored Procedures

    Uploaded by

    G.C.Reddy
    This document discusses stored procedures in SQL Server. It defines stored procedures as compiled groups of Transact-SQL statements that can accept parameters, return values, and perform database operations like calling other procedures. The benefits of stored procedures are listed as modular programming, faster execution, reduced network traffic, and security. Finally, it provides examples of creating a stored procedure to insert values into a table and executing the procedure.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    Stored Procedures

    Uploaded by

    G.C.Reddy
    400 views2 pages
    This document discusses stored procedures in SQL Server. It defines stored procedures as compiled groups of Transact-SQL statements that can accept parameters, return values, and perform database operations like calling other procedures. The benefits of stored procedures are listed as modular programming, faster execution, reduced network traffic, and security. Finally, it provides examples of creating a stored procedure to insert values into a table and executing the procedure.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses stored procedures in SQL Server. It defines stored procedures as compiled groups of Transact-SQL statements that can accept parameters, return values, and perform database operations like calling other procedures. The benefits of stored procedures are listed as modular programming, faster execution, reduced network traffic, and security. Finally, it provides examples of creating a stored procedure to insert values into a table and executing the procedure.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    400 views2 pages

    Stored Procedures

    Uploaded by

    G.C.Reddy
    This document discusses stored procedures in SQL Server. It defines stored procedures as compiled groups of Transact-SQL statements that can accept parameters, return values, and perform database operations like calling other procedures. The benefits of stored procedures are listed as modular programming, faster execution, reduced network traffic, and security. Finally, it provides examples of creating a stored procedure to insert values into a table and executing the procedure.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 2

    www.gcreddy.

    com

    STORED PROCEDURES
    A stored procedure is a group of Transact-SQL statements compiled into a single
    execution plan.

    Stored procedures in SQL Server are similar to procedures in other programming


    languages in that they can:

    • Accept input parameters and return multiple values in the form of output
    parameters to the calling procedure or batch.

    • Contain programming statements that perform operations in the database,


    including calling other procedures.

    • Return a status value to a calling procedure or batch to indicate success or


    failure (and the reason for failure).

    You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored
    procedures are different from functions in that they do not return values in place of
    their names and they cannot be used directly in an expression.

    The benefits of using stored procedures in SQL Server rather than Transact-SQL
    programs stored locally on client computers are:

    • They allow modular programming.


    • They allow faster execution.
    • They can reduce network traffic.
    • They can be used as a security mechanism.

    Stored Procedures supported by SQL Server:

    SQL Server supports five types of stored procedures. They are:

    System Stored Procedures (sp_)

    Many administrative and informational activities SQL Server can be performed


    through system stored procedures. These system stored procedures are stored in the
    Master database and are identified by the sp_prefix. They can be executed from any
    database.

    Local Stored Procedures

    These procedures will be created in the user database. The user who creates
    the procedure will become the owner for that procedure.

    Temporary Stored Procedures

    Temporary stored procedures are stored in tempdb database. They can be


    used in the case where an application builds dynamic Transact-SQL statements that
    are executed several times. Instead of recompiling the T-SQL statements each time,

    www.gcreddy.com 1
    www.gcreddy.com

    a temporary stored procedure can be created and compiled on the first execution,
    then execute the precompiled plan multiple times. The temporary stored procedures
    can be local or global.

    Remote Stored Procedures

    They are legacy feature of SQL Server. Their functionality in T-SQL is limited
    to executing a stored procedure on a remote SQL Server installation. The distributed
    queries in SQL Server support this ability along with the ability to access tables on
    linked OLEDB data sources directly from local T_SQL statements.

    Extended Stored Procedures

    These are dynamic link libraries (DLLs) that SQL Server can dynamically load
    and execute. These procedures run directly in the address space of SQL Server and
    are programmed using the SQL Server Open Data Services API. They are identified
    by the xp_prefix.

    Creating a Stored Procedure

    The stored procedures can be created using the CREATE PROCEDURE


    statement.

    Syntax:

    CREATE PROCEDURE procedure_name


    [( @parameter1 data_type [OUTPUT] [, @parameter2 …..])]
    AS
    SQL-Statements

    Example: creating a procedure to insert values into emp table.

    Create procedure empInsert


    (@eid Char,@en varchar(10),@dno int,@grd char(1),@bsal numeric(9,2),@dj
    datetime)
    AS
    Begin
    INSERT INTO emp VALUES(@eid,@en,@dno,@grd,@bsal,@dj)
    end

    Ex: Executing the Procedure:

    EXEC empInsert E001,’sam’,10,’B’,4500,’11/3/96’

    www.gcreddy.com 2

    You might also like