This article helps you to create a stored procedure on SQL Server and tells you how to use them in your asp.net applications.
Introduction Stored procedures (sprocs) are generally an ordered series of Transact-SQL statements bundled into a single logical unit. They allow for variables and parameters, as well as selection and looping constructs. A key point is that sprocs are stored in the database rather than in a separate file.
Advantages over simply sending individual statements to the server include:
Parameterization A stored procedure gives us some procedural capability, and also gives us a performance boost by using mainly two types of parameters:
From outside the sproc, parameters can be passed in either by position or reference. Declaring Parameters
The syntax is : @parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]
Let's now create a stored procedure named "Submitrecord". First open Microsoft SQL Server -> Enterprise Manager, then navigate to the database in which you want to create the stored procedure and select New Stored Procedure. See the below Stored Procedure Properties for what to enter, then click OK. Now create an application named Store Procedure in .net to use the above sprocs. Stored Procedure.aspx page code
Stored Procedure.aspx.cs page code
When we run the application, the window will look like this: After clicking the submit button the data is appended to the database as seen below in the SQL Server table record:
SQL Server 2012 Functions Recipes