http://zxianf.blog.163.com/blog/static/301207012009114104124969/中片关于Sql Server中存储过程output和return值的区别

在里面有讲解,我在自己本机中测试的结果如下,

1:ReturnValue只能返回0,1,-1这样的数据,局限性很大 ,而在存储过程中用OutPut参数,可以返回各种类型的数据,比较灵活方便。

ReturnValue   是用来返回错误码的,output是指存储过程传出参数       例如    :

@Flag   varchar(20)   output   
View Code
 1 sql存储过程:
2 create proc Test
3 @B varchar(50) output,
4 @C varchar(50)
5 as
6 begin
7 declare @A int
8 set @B=@C+'Return'
9 set @A=1000
10 return @A
11 end


c#程序代码:       
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 //调用sqlhelper时这样写,单独不行:comm.Parameters.Add(不能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();
结果为: result1=1000;result2=insertmsgResult
另外还要主要output中如果返回字符串时候,一定需要指定字符串的长度,否则返回的时候就只返回首字符,写成下面的形式
或者指定其长度
new SqlParameter("@TableName",SqlDbType.VarChar,500,ParameterDirection.Output,false,0,0,"TableName",DataRowVersion.Default,pTable),
其中测试的语句如下
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 // 注意这里要把CommandType设为StoredProcedure解析为存储过程
21 // 也可默认为Text 以SQL语句模式解析,这样调用存储过程就要用SQL语句 EXEC <存储过程名> <参数...> 写 SQL 语句调用
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 // 这里把输出参数放到一个 HashTable 里面,方便取出
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 }
其中几个关键主要设置参数的方式和和取得返回值的方式。