ADO.NET 的几个示例三，事物和存储过程-优快云博客

本文链接：https://blog.youkuaiyun.com/gishero/article/details/5918510

本文介绍了一个SQL Server存储过程的创建与调用实例，包括如何通过C#代码进行参数传递并获取输出结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如题，存储过程如下

USE [Table]
GO

                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                -- =============================================
                -- Author:  <Author,,Name>
                -- Create date: <Create Date,,>
                -- Description: <Description,,>
                -- =============================================
                ALTER PROCEDURE [dbo].[myProcedure]
                 @param1 varchar(50),
                 @param2 varchar(50),
                 @paramReturn varchar(50) OUTPUT
                AS
                BEGIN
                 -- SET NOCOUNT ON added to prevent extra result sets from
                 -- interfering with SELECT statements.
                 SET NOCOUNT ON;

                    -- Insert statements for procedure here
                 SELECT A.USER_ID,A.WORKER_ID FROM T_USER A WHERE A.USER_ID = @param1
                 SET @paramReturn = 'return string'

                 --返回值,isnull如果返回值是NULL则返回空
                 RETURN isnull(@paramReturn,'')
                END

调用代码

/// <summary> /// 存储过程及带参数的存储过程调用 /// </summary> void CreateSqlTransaction() { SqlConnection conn; SqlTransaction tran; conn = new SqlConnection("Data Source=localhost;Initial Catalog=Table;Integrated Security=SSPI;"); conn.Open(); tran = conn.BeginTransaction(); SqlCommand comm = conn.CreateCommand(); comm.Transaction = tran; comm.CommandType = CommandType.StoredProcedure; comm.CommandText = "myProcedure"; comm.Parameters.AddWithValue("@param1", "12345"); comm.Parameters.AddWithValue("@param2", ""); comm.Parameters.AddWithValue("@paramReturn", ""); comm.Parameters[2].Direction = ParameterDirection.Output; SqlDataReader reader = comm.ExecuteReader(); reader.Read(); MessageBox.Show("WORKERID ：" + reader[1]);// reader.GetString(1) MessageBox.Show("param2　：" + comm.Parameters[1].Value.ToString());//存储过程的参数必须定义，不论使用与否 MessageBox.Show("paramReturn　：" + comm.Parameters[2].Value.ToString()); reader.Close(); tran.Commit(); conn.Close(); }