Differences between output return of stored procedures in SQL Server

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.