Because the general use of T-SQL to execute database operations, occasionally now to use the stored procedure, did not write the return value of the call process, check a lot of information, are not detailed, or not to the point, maybe I am stupid.
C # source code 1 // <summary>
2 // verify the user
3 /// </Summary>
4 // <Param name = "user"> User Information </param>
5 /// <returns> </returns>
6 public static int verifyuser (clsuser user)
7 {
8 int iret;
9 string SQL = string. format ("execute verifyuser @ myverifyreader output, '{0}', '{1}'", user. username, user. password); // The T-SQL string for execution
10 sqlcommand scmd = new sqlcommand (SQL, Conn );
11 scmd. Parameters. Add (New sqlparameter ("@ myverifyreader", sqldbtype. INT ));
12 scmd. Parameters ["@ myverifyreader"]. Direction = parameterdirection. output;
13 //
14
15 dbopen (conn );
16 // there is a problem with the returned value
17 scmd. executenonquery ();
18 iret = (INT) scmd. Parameters ["@ myverifyreader"]. value;
19 dbclose (conn );
20 return iret;
21}
SQL Server Stored Procedure ------------------------------
/*
* Verifyuser Stored Procedure
* Purpose: Verify User Login
*/
Create procedure verifyuser
(
@ Myverifyreader int output, -- return result
@ Username varchar (50), -- User Name
@ Userpassword varchar (50) -- User Password
)
As
If exists (select [ID] from [users] Where username = @ username and userpassword = @ userpassword)
Set @ myverifyreader = 0 -- Verification passed
Else if exists (select [ID] from [users] Where username = @ username)
Set @ myverifyreader = 1 -- the user exists and the password is incorrect.
Else
Set @ myverifyreader = 2 -- the user does not exist
Return isnull (@ myverifyreader, 3)
-------------------------------------------------
Go
Let's write it down first. That's how it is used.