Stored Procedures

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.

EXECUTION

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;

SEND PARAMETERS

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));
« Previous
Next »