Updating a database stored procedure
One especially versatile extended stored procedure is xp_cmdshell, which executes a command string as an OS command shell.(Essentially, you can run from xp_cmdshell any command that you can run from the Win2K/NT command line.) To create a stored procedure, you must be the DBO or systems administrator (sa), or be a member of the db_ddladmin role.A stored procedure is compiled code that you can call from within T-SQL statements or from client applications.SQL Server runs the code in the procedure and then returns the results to the calling application.You can create tables—both permanent and temporary—but any temporary objects you create within a stored procedure vanish when the stored procedure is complete.And you can nest stored procedures up to 32 levels deep, so that one procedure can call another, which calls a third, and so on.If all the client applications use the same stored procedures to update the database, the code base is smaller and easier to maintain, and you run less risk of deadlocks because everyone is updating tables in the same order.Stored procedures enhance security, too, because you can give the users only EXECUTE permission on the stored procedures, while restricting access to the tables and not allowing the users any direct update privileges.
You can use stored procedures to enforce a level of consistency in your client applications.
Here's a skeleton example to see a stored procedure in action: CREATE PROCEDURE Withdraw /* Routine name */ (parameter_amount DECIMAL(6,2), /* Parameter list */ parameter_teller_id INTEGER, parameter_customer_id INTEGER) MODIFIES SQL DATA /* Data access clause */ BEGIN /* Routine body */ UPDATE Customers SET balance = balance - parameter_amount WHERE customer_id = parameter_customer_id; UPDATE Tellers SET cash_on_hand = cash_on_hand parameter_amount WHERE teller_id = parameter_teller_id; INSERT INTO Transactions VALUES ( parameter_customer_id, parameter_teller_id, parameter_amount); END; See CREATE PROCEDURE for full syntax details. Banks commonly use stored procedures so that applications and users don't have direct access to the tables.
Stored procedures are also useful in an environment where multiple languages and clients are all used to perform the same operations.
An extended stored procedure is a DLL (typically written in C or C ) that leverages the integration of SQL Server and Windows 2000/NT to make OS-level calls and perform functions that are beyond T-SQL's scope.
Both types can accept input parameters and return output values, error codes, and status messages.