Differences between output return of stored procedures in SQL Server

See http://zxianf.blog.163.com/blog/static/301207012009111084124969/the difference between output and return values of stored procedures in SQL Server.

The following is the test result on my local machine,

1: returnvalue can only return data such as 0, 1,-1, with great limitations. In the stored procedure, the output parameter can return various types of data, which is flexible and convenient.

Returnvalue is used to return error codes. output refers to the output parameters in the stored procedure, for example:

@Flag   varchar(20)   output   

View code

1. SQL stored procedure:
2 create proc Test
3 @ B varchar (50) output,
4 @ C varchar (50)
5
6 begin
7 declare @ A int
8 set @ B = @ C + 'Return'
9 set @ A = 1000
10 return @
11 end

C # program code: view code

1 system. Data. sqlclient. sqlconnection conn = new system. Data. sqlclient. sqlconnection ("Server = (local); uid = sa; Pwd = sa; database = serveruforvhost1 ");
2 system. Data. sqlclient. sqlcommand comm = new system. Data. sqlclient. sqlcommand ("test", Conn );
3 comm. commandtype = system. Data. commandtype. storedprocedure;
4 // when sqlhelper is called, it cannot be written separately: comm. Parameters. Add (cannot addsqlparameter [])
5 // sqlparameter [] parameter = {
6 // new system. Data. sqlclient. sqlparameter ("@ A", system. Data. sqldbtype. Int, 4 ),
7 // new system. Data. sqlclient. sqlparameter ("@ B", system. Data. sqldbtype. varchar, 50 ),
8 // new system. Data. sqlclient. sqlparameter ("@ C", system. Data. sqldbtype. varchar, 50)
9 //};
10 // parameter [2]. Direction = parameterdirection. input;
11 // parameter [0]. Direction = parameterdirection. returnvalue;
12 // parameter [1]. Direction = parameterdirection. output;
13 comm. Parameters. Add (new system. Data. sqlclient. sqlparameter ("@ A", system. Data. sqldbtype. Int, 4 ));
14 comm. Parameters ["@ A"]. Direction = parameterdirection. returnvalue;
15 comm. Parameters. Add (new system. Data. sqlclient. sqlparameter ("@ B", system. Data. sqldbtype. varchar, 50 ));
16 comm. Parameters ["@ B"]. Direction = parameterdirection. output;
17 comm. Parameters. Add (new system. Data. sqlclient. sqlparameter ("@ C", system. Data. sqldbtype. varchar, 50 ));
18 comm. Parameters ["@ C"]. value = "insertmsg ";
19 conn. open ();
20 int I = comm. executenonquery ();
21 string result1 = comm. Parameters ["@ A"]. value. tostring ();
22 string result2 = comm. Parameters ["@ B"]. value. tostring ();
23 conn. Close ();

The result is: result1 = 1000; result2 = insertmsgresult. In addition, the length of the string must be specified when the string is returned. Otherwise, only the first character is returned, specify the length of New sqlparameter ("@ tablename", sqldbtype. varchar, 500, parameterdirection. output, false, 0, 0, "tablename", datarowversion. default, ptable). The test statement is as follows:

View code

1 Public void testoutput (out string ptable, out int ppageindex, out int ptotalpage)
2 {
3 ptable = string. empty;
4 ppageindex = 0;
5 ptotalpage = 0;
6 string procedurename = "up_pageoutput ";
7 system. Collections. hashtable result = new system. Collections. hashtable ();
8 //
9 try
10 {
11 using (sqlconnection connection = new sqlconnection (sqlhelper. sqlhelper. connectionstringlocaltransaction ))
12 {
13 connection. open ();
14 if (connection. State! = Connectionstate. open)
15 {
16 connection. open ();
17}
18 using (sqlcommand cmd = new sqlcommand (procedurename, connection ))
19 {
20 // note that commandtype should be set to storedprocedure and parsed as a stored procedure
21 // text can also be parsed in SQL statement mode by default. Therefore, to call a stored procedure, use SQL statement exec <Stored Procedure Name> <parameter...> to write an SQL statement call.
22 cmd. commandtype = commandtype. storedprocedure;
23 cmd. commandtimeout = 60;
24 cmd. Parameters. addrange (New sqlparameter []
25 {
26 new sqlparameter ("@ tablename", sqldbtype. varchar, 500, parameterdirection. Output, false, 0, 0, "tablename", datarowversion. Default, ptable ),
27 // new sqlparameter ("@ pageindex", sqldbtype. Int, ppageindex ),
28 new sqlparameter ("@ pageindex", ppageindex ),
29 // new sqlparameter ("@ totalpage", sqldbtype. Int, ptotalrecord)
30 new sqlparameter ("@ totalpage", ptotalpage)
31 });
32 cmd. Parameters ["@ tablename"]. Direction = parameterdirection. output;
33 cmd. Parameters ["@ pageindex"]. Direction = parameterdirection. output;
34 cmd. Parameters ["@ totalpage"]. Direction = parameterdirection. output;
35 cmd. Parameters. Add (New sqlparameter ("@ retrunvalue", sqldbtype. varchar, 500 ));
36 cmd. Parameters ["@ retrunvalue"]. Direction = parameterdirection. returnvalue;
37 Object Hang = cmd. executenonquery ();
38 foreach (sqlparameter Param in cmd. Parameters)
39 {
40 // put the output parameter in a hashtable for easy retrieval
41 if (Param. Direction = parameterdirection. Output | Param. Direction = parameterdirection. InputOutput | Param. Direction = parameterdirection. returnvalue)
42 {
43 result. Add (Param. parametername, Param. value );
44}
45}
46 // ptotalrecord = sqlhelper. sqlhelper. executenonquery (sqlhelper. sqlhelper. connectionstringlocaltransaction, commandtype. storedprocedure, procedurename, Param );
47 object retvalue1 = cmd. Parameters ["@ totalpage"]. value;
48 // ptotalpage = convert. toint32 (retvalue1 );
49 Object retvalue2 = cmd. Parameters ["@ pageindex"]. value;
50 Object retvalue3 = cmd. Parameters ["@ tablename"]. value;
51 object retvalue4 = cmd. Parameters ["@ retrunvalue"]. value;
52
53}
54
55 connection. Close ();
56}
57}
58 catch (exception)
59 {
60
61}
62}

Several key parameters are mainly set and returned values are obtained.