A stored procedure is a routine written in T-SQL by using the DML, which acts on a rows of a table in a database. SQL does not support IF statements and functions which manipulate strings, formatting functions, whereas T-SQL supports all of them. Stored procedures are stored in SQL Server databases. We can use stored procedures to build business rules into the database. After stored procedures have been stored to the database, users and applications can call them as if they were another SQL statement or a built-in T-SQL function. The main advantage of using stored procedures is performance. Stored procedures execute on the database server, close to the data. Stored procedures which are nothing but data manipulation code, execute faster than passing SQL statements fromVB. A stored procedure can scan thousands of records, perform calculations, and return a single number to an application. All calculations occur on the database server and data is not moved across the network. For this reason stored procedures are faster than equivalent SQL statements. After stored procedures are defined, they become part of the database and appear as database objects, like tables or views. This makes it easier to access the stored procedure statements and manipulate them. Once they are tested, stored procedures do not require compiling as is the case with VB code. We can set security on the stored procedure so that only callers with appropriate permissions can execute the logic. Using stored procedures we can encapsulate the business logic, hiding database structure changes from an application. We can also change the structure of the underlying tables, by modifying the stored procedure without affecting applications that use the stored procedure. By providing a stored procedure for editing the transactions, we can ensure the integrity of the data.
Creating and Executing Stored ProceduresWe can use the SQL Server Query Analyzer or the Enterprise Manager to write, debug, and execute stored procedures against a database. To create a stored procedure, we enter the definition of the procedure into the database. We create a new stored procedure by using the CREATE PROCEDURE statement and save it to the database. This step does not actually execute the stored procedure. To execute a procedure saved into the database, we must use the EXECUTE statement.
Triggers are special types of stored procedures. Triggers are very important in SQL programming. A trigger is a procedure that SQL Server invokes automatically when certain changes applied to data. These actions are inserting, deleting, and updating a row. We can think of triggers as VB event handlers for onUpdate, onInsert, and onDelete events. Creating a trigger is similar to creating a stored procedure.
I have put up a detailed set of articles about web services, including how to create one at:
http://www.vkinfotek.com/webservice/createwebservce.aspx Below is a roadmap for a programmer to graduate from a beginner to an expert.
