SQL Server Stored Procedure

1. Simple instances

1. First, let's look at the simplest stored procedure.
Create procedure dbo. testProcedure_AX
AS
Select userID from USERS order by userid desc
Note: dbo. testProcedure_AX is the name of the stored procedure you created. You can change it to AXzhz and so on. Do not conflict with keywords. AS is an SQL statement.

2. How to call this stored procedure in ASP. NET?
Public static string GetCustomerCName (ref ArrayList arrayCName, ref ArrayList arrayID)
{
SqlConnection con = ADConnection. createConnection ();
SqlCommand cmd = new SqlCommand ("testProcedure_AX", con );
Cmd. CommandType = CommandType. StoredProcedure;
Con. Open ();
Try
{
SqlDataReader dr = cmd. ExecuteReader ();
While (dr. Read ())
{
If (dr [0]. ToString () = "")
{
ArrayCName. Add (dr [1]. ToString ());
}
}
Con. Close ();
Return "OK! ";
}
Catch (Exception ex)
{
Con. Close ();
Return ex. ToString ();
}
}
Note:
SqlCommand cmd = new SqlCommand ("select userID from USERS order by userid desc", con );
Replace the SQL statement with the stored procedure name, and mark the cmd type as CommandType. StoredProcedure (stored procedure)

3. Another stored procedure with parameters.
Create procedure dbo. AXzhz
/*
Write comments here
*/
@ StartDate varchar (16 ),
@ EndDate varchar (16)
AS
Select id from table_AX where commentDateTime> @ startDate and commentDateTime <@ endDate order by contentownerid DESC

Note: @ startDate varchar (16) declares the variable @ startDate. Multiple Variable names are separated by [,]. The variable can be used in the subsequent SQL statements.

4. How to call this stored procedure with parameters in ASP. NET?
Public static string GetCustomerCNameCount (string startDate, string endDate, ref DataSet ds)
{
SqlConnection con = ADConnection. createConnection ();
// ----------------------- Pay attention to this section --------------------------------------------------------------------------
SqlDataAdapter da = new SqlDataAdapter ("AXzhz", con );

Para0 = new SqlParameter ("@ startDate", startDate );
Para1 = new SqlParameter ("@ endDate", endDate );
Da. SelectCommand. Parameters. Add (para0 );
Da. SelectCommand. Parameters. Add (para1 );
Da. SelectCommand. CommandType = CommandType. StoredProcedure;
// Configure //-----------------------------------------------------------------------------------------------------------
Try
{
Con. Open ();
Da. Fill (ds );
Con. Close ();
Return "OK ";
}
Catch (Exception ex)
{
Return ex. ToString ();
}
}

Note: If you add the command parameters, it will be OK.

5. Check whether the SQL command is successfully executed.
Create procedure dbo. AXzhz
/*
@ Parameter1 User Name
@ Parameter2 New Password
*/
@ Password nvarchar (20 ),
@ UserName nvarchar (20)
AS
Declare @ err0 int
Update WL_user set password = @ password where UserName = @ userName
Set @ err0 =@@ error
Select @ err0 as err0

Note: declare an integer variable @ err0 first, and assign the value @ error to it (whether the statement automatically given by the system is successfully executed, 0 is successful, and others are failed ), finally, select it through select.

6. How to obtain the execution success value from the background?
The following code tells you the answer:
Public static string GetCustomerCName ()
{
SqlConnection con = ADConnection. createConnection ();
SqlCommand cmd = new SqlCommand ("AXzhz", con );
Cmd. CommandType = CommandType. StoredProcedure;
Para0 = new SqlParameter ("@ startDate", "2006-9-10 ");
Para1 = new SqlParameter ("@ endDate", "2006-9-20 ");
Da. SelectCommand. Parameters. Add (para0 );
Da. SelectCommand. Parameters. Add (para1 );
Con. Open ();
Try
{
Int32 re = (int32) cmd. ExecuteScalar ();
Con. Close ();
If (re = 0)
Return "OK! ";
Else
Return "false ";
}
Catch (Exception ex)
{
Con. Close ();
Return ex. ToString ();
}
}

7. How can I determine which SQL statement to execute based on input parameters?
Alter procedure dbo. selectCustomerCNameCount
@ CustomerID int
AS
If @ customerID =-1
Begin
Select contentownerid, userCName, count (*) as countAll from view_usercomment group by contentownerid, userCName order by contentownerid DESC
End
Else
Begin
Select contentownerid, userCName, count (*) as countAll from view_usercomment where contentownerid = @ customerID group by contentownerid, userCName order by contentownerid DESC
End

II,Use stored procedures with returned values in C #

For example, when adding new data to a database, you need to check whether there are duplicates.
This example describes how to put the moderation process in the stored procedure and use the program to call the moderation result to make a response.
The stored procedure is as follows:
Create procedure DInstitute_Insert
@ InstituteNO nvarchar (6), @ InstituteName nvarchar (40)
AS
Declare @ return int, @ count int
If (ltrim (rtrim (@ brief utename) = ''or ltrim (rtrim (@ brief uteno) = '')
Select @ return = 3 -- 3 indicates that the submitted data has a null value.
Else
Begin
Select @ count = count (1) from DInstitute where export uteno = @ export uteno
If (@ count> 0)
Select @ return = 1 -- return 1 indicates that the number is repeated.
Else
Begin
Insert into DInstitute (InstituteNO, InstituteName) values (@ InstituteNO, @ InstituteName)
If (@ error> 0)
Select @ return = 2 -- return 2 indicates data operation Error
Else
Select @ return = 0 -- return 0 indicates that the data operation is successful.
End
End
Return @ return
GO

DInstitute is a school information table. Only InstituteNO (School number) and InstituteName (school name) fields are supported.

The code for calling the stored procedure in C # is as follows:
// Execute the insert operation
SqlCommand com1 = new SqlCommand ("DInstitute_Insert", DBcon );
If (com1.Connection. State = ConnectionState. Closed)
Com1.Connection. Open ();
Com1.CommandType = CommandType. StoredProcedure;
Com1.Parameters. Add (new SqlParameter ("@ InstituteNO", SqlDbType. NVarChar, 6 ));
Com1.Parameters. Add (new SqlParameter ("@ InstituteName", SqlDbType. NVarChar, 40 ));
Com1.Parameters. Add (new SqlParameter ("@ return", SqlDbType. Int ));
Com1.Parameters ["@ return"]. Direction = ParameterDirection. ReturnValue;
Com1.Parameters ["@ InstituteNO"]. Value = t_NO.Text;
Com1.Parameters ["@ InstituteName"]. Value = t_name.Text;
Try
{
Com1.ExecuteScalar ();
}
Catch (SqlException ee)
{
DB. msgbox ("operation failed! "+ Ee. Message. ToString ());
Return;
}
Finally
{
Com1.Connection. Close ();
}
String temp = com1.Parameters ["@ return"]. Value. ToString ();
// If 0 is returned, the data operation is successful.
// Return 1, indicating that the number is repeated.
// 2 indicates data operation Error
// 3 indicates that the submitted data has a null value.
Switch (temp)
{
Case "0 ":
DB. msgbox ("added successfully! ");
Break;
Case "1 ":
DB. msgbox ("number already exists! ");
Break;
Case "2 ":
DB. msgbox ("data operation error! ");
Break;
Case "3 ":
DB. msgbox ("the submitted data has a null value! ");
Break;
}
Binding (); // refresh the datagrid

III,SQL Server Stored Procedure Transaction Processing

Method 1:
-- Test table
Create table tb (id int not null constraint PK_sys_zj_fielddict primary key, aa int)
-- Transaction Processing
Begin tran
Insert into tb values (1, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (1, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (2, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (2, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (3, 1)
If @ error <> 0 goto lb_rollback
Lb_commit:
Commit tran
Goto lb_ OK
Lb_rollback:
Rollback tran
-- Display Results
Lb_ OK:
Select * from tb
Drop table tb

Method 2:
-- Create a test table
Create table tb (id int not null constraint PK_sys_zj_fielddict primary key, aa int)
-- Set options
SET XACT_ABORT on
-- Transaction Processing
Begin tran
Insert into tb values (1, 1)
Insert into tb values (1, 1)
Insert into tb values (2, 1)
Commit tran
-- Display Results
/* -------- Note
If this is the case, the subsequent statements will not be executed. If you want to execute the following statements, you must add GO to the end of the sentence, which is only supported by the query analysis analyzer, therefore, in the stored procedure, make sure that there are no other statements after commit tran. Otherwise, other statements will not be executed when an error occurs.
-----------*/
Select * from tb
Drop table tb

4. Use transactions in. Net

SqlConnection myConnection = new SqlConnection ("Data Source = localhost; Initial Catalog = Northwind; Integrated Security = SSPI ;");
MyConnection. Open ();

SqlTransaction myTrans = myConnection. BeginTransaction (); // use New to generate a transaction
SqlCommand myCommand = new SqlCommand ();
MyCommand. Transaction = myTrans;

Try
{
MyCommand. CommandText = "Update Address set location = '23 rain street 'where userid = '000000 '";
MyCommand. ExecuteNonQuery ();

MyCommand. CommandText = "Update table2 set dd = '23 rain street 'where userid = '000000 '";
MyCommand. ExecuteNonQuery ();

MyTrans. Commit ();
Console. WriteLine ("Record is udated .");
}
Catch (Exception e)
{
MyTrans. Rollback ();
Console. WriteLine (e. ToString ());
Console. WriteLine ("Sorry, Record can not be updated .");
}
Finally
{
MyConnection. Close ();
}

Note: In SqlServer, each SQL statement is executed as a transaction, so it is stored in the stored procedure or in.. net Code. It is not necessary to use transactions to execute a single SQL statement.

Information Source: http://www.cnblogs.com/tuyile006/