Create a stored procedure

UserAccount
Userid	UserName	PassWord	Registertime	Registerip
12	6	6	2012-12-31	6
18	5	5	2013-01-01	5
19	1	1	2013-01-01	1
20	2	2	2013-01-01	2
21st	3	3	2013-01-01	3
22	4	4	2013-01-01	4
23	5	5	2013-01-01	5
25	7	7	2013-01-01	7
26	8	8	2013-01-01	8
Null	Null	Null	Null	Null

For the table above, I use a stored procedure to do something about it:

1. A stored procedure that returns only a single record set

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure Getuseraccountasselect * from Useraccountgo------------- execute the above stored procedure ----------------exec Getuseraccount

Result: equivalent to running SELECT * from UserAccount This line of code, resulting in data for the entire table.

2. Stored procedures without input and output

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure Inuseraccountasinsert into UserAccount (Username,[password],registertime,registerip) VALUES (9,9, ' 2013-01-02 ', 9) Go------------- Execute the above stored procedure ----------------exec inuseraccount

Result: equivalent to running INSERT into UserAccount (Username,[password],registertime,registerip) VALUES (9,9, ' 2013-01-02 ', 9) this line of code.

3. Stored procedure with return value

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure Inuseraccountreasinsert into UserAccount (Username,[password],registertime,registerip) VALUES (10,10, ' 2013-01-02 ', return @ @rowcountgo------------- Execute the above stored procedure ----------------exec inuseraccountre

Explanation: Here's @ @rowcount The number of rows affected by the execution of the stored procedure, with the result of not only inserting a single piece of data, but also returning a value return value =1 , this can be obtained in the program, and later in the C # the call to the stored procedure will be said.

4. Stored procedures with input parameters and output parameters

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure [email Protected] nchar, @UserID int outputasif (@UserName >5) Select @UserID =count (*) from UserAccount where userid> 25elseset @UserID =1000go------------- Execute the above stored procedure ----------------exec getuseraccountre ' 7 ', null

Explanation:@UserName as an input parameter,@UserID as an output parameter. The result is @userID coout(*) or =1.

5. Stored procedure with return value, input parameter, output parameter

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure [email Protected] nchar, @UserID int outputasif (@UserName >5) Select @UserID =count (*) from UserAccount where userid> 25elseset @UserID =1000return @ @rowcountgo------------- perform the above stored procedure ----------------exec GetUserAccountRe1 ' 7 ', null

results: The @userID was coout(*) or =1,retun value= 1.

6. Simultaneous return of parameters and record set stored procedures

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure [email Protected] nchar, @UserID int outputasif (@UserName >5) Select @UserID =count (*) from UserAccount where userid> 25elseset @UserID =1000select * from Useraccountreturn @ @rowcountgo------------- Execute the above stored procedure -------------- --exec GetUserAccountRe2 ' 7 ', null

Result: Returns the result set of the code that executes the select * from UserAccount, and @userID to coout( * ) is =1 , Retun value=9 .

7. A stored procedure that returns multiple recordsets

------------- Create A stored procedure named Getuseraccount ----------------Create Procedure Getuseraccountre3asselect * from Useraccountselect * from UserAccount where Userid>5go------------- execute the above stored procedure ----------------exec GetUserAccountRe3

Result: Returns two result sets, one for select * from UserAccountand the other for select * from UserAccount where userid>5 。

Summary: We've created a variety of stored procedures above, and see how we call these stored procedures in C # .

Create a stored procedure