locked
stored procedures RRS feed

  • Question

  • User160031809 posted

    I am total beginner in ASP.net field.

    I have a question: Whats the role of stored procedures  in database ?

     

    Thursday, July 6, 2006 7:49 PM

All replies

  • User-442214108 posted

    A stored procedure is a a set of SQL statements stored under a procedure name so that the statements can be executed as a group by the database server

    even though I said 'set of statements', it can be one or more, or it can have transactions that, if a problem happens along the way, it can be rolled back as if nothing really happened.

    it's stored physically in the database - so that, when it is run, it is run directly by the database engine itself, and that makes it faster at processing

    Thursday, July 6, 2006 7:54 PM
  • User160031809 posted

    Thanks.

    Could you also tell me about any website where i learn how to write stored procedures ?

    Friday, July 7, 2006 10:55 AM
  • User1416329745 posted

    I am total beginner in ASP.net field.

    I have a question: Whats the role of stored procedures  in database ?

    Stored Procedures are compiled SQL statements used by RDBMS(relational database management systems) to INSERT, UPDATE, DELETE and return data from the database.  Try the link below and download the T-SQL tutorial to get started.  Hope this helps.

    http://www.mssqlserver.com/tsql/

    Friday, July 7, 2006 11:10 AM
  • User1838126768 posted

    http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=49

    http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=127&lngWId=5

    Or just use Google - - I just did a Google search using the following and got quite a few pages returned:

    "SQL Server" "stored procedure" tutorial

    Copy the above phrase, exactly, including the quotes, to search.

    Friday, July 7, 2006 11:13 AM
  • User160031809 posted

    Thanks for the link.

     

     

     

    I am total beginner in ASP.net field.

    I have a question: Whats the role of stored procedures  in database ?

    Stored Procedures are compiled SQL statements used by RDBMS(relational database management systems) to INSERT, UPDATE, DELETE and return data from the database.  Try the link below and download the T-SQL tutorial to get started.  Hope this helps.

    http://www.mssqlserver.com/tsql/

    Friday, July 7, 2006 11:33 AM
  • User160031809 posted

    Thanks for the link and info about searching.

     

    http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=49

    http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=127&lngWId=5

    Or just use Google - - I just did a Google search using the following and got quite a few pages returned:

    "SQL Server" "stored procedure" tutorial

    Copy the above phrase, exactly, including the quotes, to search.

    Friday, July 7, 2006 11:35 AM
  • User1177242335 posted

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

    Microsoft® SQL Server™ 2000 stored procedures return data in four ways:

        * Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).

        * Return codes, which are always an integer value.

        * A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.

        * A global cursor that can be referenced outside the stored procedure.

    Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

    Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. The IF statement in this example shows embedding conditional logic in a procedure to keep from sending a result set to the application:

    IF (@QuantityOrdered < (SELECT QuantityOnHand
                      FROM Inventory
                      WHERE PartID = @PartOrdered) )
       BEGIN
       -- SQL statements to update tables and process order.
       END
    ELSE
       BEGIN
       -- SELECT statement to retrieve the IDs of alternate items
       -- to suggest as replacements to the customer.
       END

    Applications do not need to transmit all of the SQL statements in the procedure: they have to transmit only an EXECUTE or CALL statement containing the name of the procedure and the values of the parameters.

    Stored procedures can also shield users from needing to know the details of the tables in the database. If a set of stored procedures supports all of the business functions users need to perform, users never need to access the tables directly; they can just execute the stored procedures that model the business processes with which they are familiar.

    An illustration of this use of stored procedures is the SQL Server system stored procedures used to insulate users from the system tables. SQL Server includes a set of system stored procedures whose names usually start with sp_. These system stored procedures support all of the administrative tasks required to run a SQL Server system. You can administer a SQL Server system using the Transact-SQL administration-related statements (such as CREATE TABLE) or the system stored procedures, and never need to directly update the system tables.

    Friday, April 22, 2011 5:30 AM
  • User721740140 posted

    Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

    Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

    Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.

    Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

     

    Friday, May 13, 2011 1:08 AM