stored procedures in SQL Server stored procedure creation and C # calls

A stored procedure is a compiled, optimized set of SQL statements placed on the database server, which can be called directly by the application. There are several advantages to using stored procedures:
1. Faster execution speed than normal SQL statements
2, easy to centralized control
3, can reduce the network traffic
4, ensure the security and integrity of the database
5. Flexibility

create a stored procedure
You can use Transact-SQL statements to create a stored procedure stored procedured. Databases->database name->programmability->stored in Microsoft SQL Server Management Studio Procedures right-click stored procedure generates a template that creates a stored procedure, modifies its contents and then executes it to create stored procedured.

Let me first take the example of creating a stored procedure that inserts data into a table. For example, my original table was created by the following statement:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N‘Persons‘) AND OBJECTPROPERTY(id,N‘isUserTable‘) = 1) CREATE TABLE Persons (Id_P int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))

Stored procedure the SQL to create the Insert feature is as follows:

Use [Databasename]go/****** object:storedprocedure [dbo].    [Insert_persions] Script date:2/25/2015 11:14:11 AM ******/ set ansi_nulls ongoSET quoted_identifier ongoIF OBJECT_ID (' insert_persons ',' P ')   is not NULLDROP PROCEDURE insert_persons;    GO-- =============================================--Author: <Author,,Name>--Create Date: <create date,,>--Description: <Description,,>-- =============================================CREATE PROCEDUREInsert_persons--ADDThe parameters forThe storedprocedureHere @idint, @lastnamevarchar(255), @firstnamevarchar(255), @adressvarchar(255), @cityvarchar(255) asBEGIN--SETNOCOUNT onAdded toPrevent extra result Sets from--interfering with SELECTStatements.SETNOCOUNT on;    --Insert statements for procedure here     INSERT Persons (id_p, LastName, FirstName, Address, city) VALUES (@id, @lastname, @firstname, @ad Ress, @city)ENDGO 

The above code completes the first check whether stored Procedure exists, if there is a first to delete the stored Procedure, and then create the stored Procedure. The function is to insert a record by passing in the parameters. Click Execute directly or press F5 the above code is executed, the corresponding database will have a stored procedure name is insert_persons. Take SQL Server 2014 as an example, as long as the corresponding database is refreshed, and then to programmability->stored procedures under the discovery of more than a dbo.insert_persons.

Executing stored procedures
Locate the appropriate stored procedure under Stored procedures, right-click to select Execute Stored Procedure ... You can create a new dialog box, fill in the input parameters and click OK to finish. Going back to the corresponding table will find a row of data.

Of course we can also execute stored procedures through SQL statements.

EXECUTE5,"Wang","San","Zhongguancun","Beijing"EXECUTE@id6,@firstname"Wang",@lastname"San",@adress"Zhongguancun",@city"Beijing"

C # Executing stored procedures
The following code is a simple demonstration of how to use C # to execute the stored procedure created above:

String Constr = @"Data source=host\sqlexpress;initial catalog=dbtan;integrated Security=sspi";SqlConnection con = new SqlConnection (CONSTR);try {con. Open();Console. WriteLine("Connect sucess!");SqlCommandcom= new SqlCommand ();                com. CommandType= System. Data. CommandType. StoredProcedure;                com. Connection= Con;                com. CommandText="Insert_persons";SqlParameter pId = new SqlParameter ("id",Ten);SqlParameter plastname = new SqlParameter ("LastName","Last");SqlParameter pfirstname = new SqlParameter ("FirstName","First");SqlParameter padress = new SqlParameter ("Adress","Pop");SqlParameter pcity = new SqlParameter ("City","Hangzhou");                com. Parameters. ADD(pId);                com. Parameters. ADD(Plastname);                com. Parameters. ADD(Pfirstname);                com. Parameters. ADD(padress);                com. Parameters. ADD(pcity);                com. ExecuteNonQuery();} catch (Exception e) {Console. WriteLine(E. ToString());} finally {con. Close();} Console. ReadKey();

stored procedures in SQL Server stored procedure creation and C # calls