Stored procedures are pre-defined, reusable routines stored within a database.
SQL Server compiles stored procedures, making them efficient options with regard to performance benefits. When possible, opt for stored procedures rather than dynamically-built queries. Use of stored procedures requires modification of the command object.
The SqlCommand object, in addition to commands from strings, executes stored procedures. It requires two settings for use with stored procedures: specify the procedure and specify it as a stored procedure. Review an example of each task below:
//Identify the procedure SqlCommand cmd = new SqlCommand(“Top 10 Largest Contracts”, conn); //Instruct the command object to execute it cmd.CommandType = CommandType.StoredProcedure;
Parameters applied to stored procedures resembles parameter use in query string commands. The example below reveals this similarity:
//A command object for identifying the procedure SqlCommand cmd = new SqlCommand(“EquipTransferHist”, conn); //Directs the command object to execute cmd.CommandType = CommandType.StoredProcedure; //Adds a parameter to the command for passing to the procedure cmd.Parameters.Add(new SqlParameter(“@EquipmentID”, equipID));