c# 调用存储过程

1. 存储过程中含有多个输出参数的示例

CREATE PROCEDURE proc_Test
    @ID INT,
    @Name VARCHAR(50),
    @Out1 INT OUTPUT,
    @Out2 VARCHAR(50) OUTPUT
AS
BEGIN
    SET @Out1=@ID
    SET @Out2=@Name
END
GO

/*Sql 调用方式*/
DECLARE @Out1 INT
DECLARE @Out2 VARCHAR(50)
EXEC Proc_Test 1,'name',@Out1 OUTPUT,@Out2 OUTPUT
SELECT @Out1,@Out2

 /*c#调用Sql原生写法*/
            SqlConnection sqlCon = new SqlConnection(SQLHelper.connectionString);
            SqlCommand sqlCmd = new SqlCommand("Proc_Test", sqlCon);
            sqlCmd.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程  

            SqlParameter sqlParme;
            //输入参数1  
            sqlParme = sqlCmd.Parameters.Add("@ID", SqlDbType.Int, 4);
            sqlParme.Direction = ParameterDirection.Input;
            sqlParme.Value = 1;
            //输入参数2  
            sqlParme = sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50);
            sqlParme.Direction = ParameterDirection.Input;
            sqlParme.Value = "name";
            //输出参数1  
            sqlParme = sqlCmd.Parameters.Add("@Out1", SqlDbType.Int, 4);
            sqlParme.Direction = ParameterDirection.Output;

            //输出参数2  
            sqlParme = sqlCmd.Parameters.Add("@Out2", SqlDbType.NVarChar, 50);
            sqlParme.Direction = ParameterDirection.Output;

            sqlCon.Open();
            sqlCmd.ExecuteNonQuery();
            int Out1 = Convert.ToInt32(sqlCmd.Parameters[2].Value);
            string Out2 = sqlCmd.Parameters[3].Value.ToString();
            sqlCon.Close();

            /*借用SqlHelper的写法*/
            SqlParameter[] parameters = {
                     SQLHelper.MakeInParam("@ID",SqlDbType.Int,4,1),
                     SQLHelper.MakeInParam("@Name",SqlDbType.NVarChar,50,"name"),
                     SQLHelper.MakeOutParam("@Out1",SqlDbType.Int,4),
                     SQLHelper.MakeOutParam("@Out2",SqlDbType.NVarChar,50)};

            int row = DAL.SQLHelper.ExecuteNonQuery(DAL.SQLHelper.connectionString, CommandType.StoredProcedure, "proc_Test", parameters);
            int numOut1 = Convert.ToInt32(parameters[2].Value);
            string strOut2 = parameters[3].Value.ToString();

2. 软件项目中的一个实例（使用游标实现while循环）

USE [TRNDB_Dev]
GO
/****** Object:  StoredProcedure [dbo].[proc_PatCheckInfo]    Script Date: 09/21/2015 14:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[proc_PatCheckInfo]
@DiagCode NVARCHAR(50),
@PEinfo NVARCHAR(4000) OUTPUT,
@AEinfo NVARCHAR(4000) OUTPUT,
@EvaluateInfo NVARCHAR(4000) OUTPUT,
@GoalInfo NVARCHAR(4000) OUTPUT,
@NoticeInfo NVARCHAR(4000) OUTPUT
AS
DECLARE @DiseaseName NVARCHAR(50)/*疾病种类*/
DECLARE @LastDiseaseName NVARCHAR(50)/*上一条的疾病类型*/
DECLARE @ControlName NVARCHAR(50)/*控件名称*/
DECLARE @nControlType NVARCHAR(50)/*控件类型*/
DECLARE @sValue NVARCHAR(50)/*控件的值*/
/*----体检信息----*/ 
SET @LastDiseaseName=''
SET @PEinfo=''

DECLARE @PETypeName NVARCHAR(50)/*体检部位*/

DECLARE @LastPETypeName NVARCHAR(50)/*上一条的体检部位*/
DECLARE @PEContent NVARCHAR(50)/*当前条目的体检内容*/


SET @LastPETypeName=''
DECLARE CursorPE CURSOR
    FOR 
       SELECT D.sName AS DiseaseName,C.sName AS PETypeName,B.sName AS PEContentName,B.nControlType,A.sValue
   FROM dbo.dia_Pat_PEInfo A INNER JOIN dbo.dia_PEContent B ON A.PEContentID=B.PEContentID 
   INNER JOIN dbo.dia_PEType C ON B.PETypeID=C.PETypeID
   INNER JOIN dbo.dia_DiseaseType D ON C.DiseaseTypeID=D.DiseaseTypeID    
   WHERE A.DiagCode=@DiagCode
   GROUP BY D.sName,C.sName,B.sName,B.nControlType,A.sValue,C.sort
   ORDER BY C.sort 
   
   OPEN CursorPE    
            FETCH NEXT FROM CursorPE INTO @DiseaseName,@PETypeName,@ControlName,@nControlType,@sValue
            WHILE @@FETCH_STATUS=0
                BEGIN
                IF(@LastDiseaseName!=@DiseaseName)
                    BEGIN
                    SET @LastDiseaseName=@DiseaseName
                    SET @PEinfo=@PEinfo+'#'+@DiseaseName
                    END                    
                IF(@LastPETypeName!=@PETypeName)
                    BEGIN
                    SET @LastPETypeName=@PETypeName
                    SET @PEinfo=@PEinfo+'$'+@PETypeName
                    END                
                IF(@nControlType='1') 
                    BEGIN
                    SET @PEContent=@ControlName
                    END
                ELSE IF(@nControlType='2') 
                    BEGIN
                    SET @PEContent=@ControlName+'&'+@sValue
                    END
                ELSE IF(@nControlType='3') 
                    BEGIN
                    SET @PEContent=@ControlName+'&'+@sValue
                    END
                    SET @PEinfo=@PEinfo+'*'+@PEContent                
            FETCH NEXT FROM CursorPE INTO @DiseaseName,@PETypeName,@ControlName,@nControlType,@sValue
                END    
    CLOSE CursorPE
    DEALLOCATE CursorPE    
    /*----End 体检信息----*/ 
    
    
    /*----辅检信息----*/ 
SET @AEinfo=''
SET @LastDiseaseName=''

DECLARE @AETypeName NVARCHAR(50)/*体检部位*/
DECLARE @AEContent NVARCHAR(500)/*空间名称*/

DECLARE CursorAE CURSOR
    FOR 
         SELECT D.sName AS DiseaseName,C.sName AS AETypeName,B.sContent AS AEContent
   FROM dbo.dia_Pat_AEInfo A INNER JOIN dbo.dia_AEContent B ON A.AEContentID=B.AEContentID
   INNER JOIN dbo.dia_AEType C ON B.AETypeID=C.AETypeID
   INNER JOIN dbo.dia_DiseaseType D ON C.DiseaseTypeID=D.DiseaseTypeID
     WHERE A.DiagCode=@DiagCode
     GROUP BY D.sName,C.sName,B.sContent
   
    OPEN CursorAE    
            FETCH NEXT FROM CursorAE INTO @DiseaseName,@AETypeName,@AEContent
            WHILE @@FETCH_STATUS=0
                BEGIN
                IF(@LastDiseaseName!=@DiseaseName)
                    BEGIN
                    SET @LastDiseaseName=@DiseaseName
                    SET @AEinfo=@AEinfo+'#'+@DiseaseName
                    END
                SET @AEinfo=@AEinfo+'$'+@AETypeName+'&'+@AEContent
                
            FETCH NEXT FROM CursorAE INTO @DiseaseName,@AETypeName,@AEContent
                END    
    CLOSE CursorAE
    DEALLOCATE CursorAE    
    /*----End 辅检信息----*/ 

/*----评定信息----*/ 
SET @EvaluateInfo=''
SET @LastDiseaseName=''
DECLARE @sVas NVARCHAR(200)
DECLARE @sJoint NVARCHAR(200)

DECLARE CursorEvaluate CURSOR
    FOR 
         SELECT sVas,sJoint 
FROM dbo.dia_Pat_EvaluateInfo A INNER JOIN dbo.dia_DiseaseType B ON A.DiseaseTypeID=B.DiseaseTypeID
  WHERE A.DiagCode=@DiagCode
   
    OPEN CursorEvaluate    
            FETCH NEXT FROM CursorEvaluate INTO @sVas,@sJoint
            WHILE @@FETCH_STATUS=0
                BEGIN
                IF(@LastDiseaseName!=@DiseaseName)
                    BEGIN
                    SET @LastDiseaseName=@DiseaseName
                    SET @EvaluateInfo=@EvaluateInfo+'#'+@DiseaseName
                    END
                SET @EvaluateInfo=@EvaluateInfo+'$'+'VAS:'+@sVas+' 关节活动度:'+@sJoint
                
            FETCH NEXT FROM CursorEvaluate INTO @sVas,@sJoint
                END    
    CLOSE CursorEvaluate
    DEALLOCATE CursorEvaluate    
    /*----End 评定信息----*/ 
    
    
    
/*----治疗目标----*/ 
SET @LastDiseaseName=''
SET @Goalinfo=''

DECLARE @TempContent NVARCHAR(200) /*每一条的模板内容*/
DECLARE CursorGoal CURSOR
    FOR 
       SELECT C.sName AS DiseaseName,B.sName AS TemplateName,B.nControlType,A.sValue 
  FROM dbo.dia_Pat_GoalInfo A INNER JOIN dbo.dia_TemplateControl B ON A.TemplateControlID=B.TemplateControlID
  INNER JOIN dbo.dia_DiseaseType C ON B.DiseaseTypeID=C.DiseaseTypeID
   WHERE A.DiagCode=@DiagCode
   GROUP BY c.sName,B.sName,B.nControlType,A.sValue,B.sort
   ORDER BY B.sort
   
   OPEN CursorGoal    
            FETCH NEXT FROM CursorGoal INTO @DiseaseName,@ControlName,@nControlType,@sValue
            WHILE @@FETCH_STATUS=0
                BEGIN
                IF(@LastDiseaseName!=@DiseaseName)
                    BEGIN
                    SET @LastDiseaseName=@DiseaseName
                    SET @Goalinfo=@Goalinfo+'#'+@DiseaseName+'$'
                    END                    
                        
                IF(@nControlType='1') 
                    BEGIN
                    SET @TempContent=@ControlName
                    END
                ELSE IF(@nControlType='2') 
                    BEGIN
                    SET @TempContent=@ControlName+'&'+@sValue
                    END
                ELSE IF(@nControlType='3') 
                    BEGIN
                    SET @TempContent=@ControlName+'&'+@sValue
                    END
                    SET @Goalinfo=@Goalinfo+'*'+@TempContent                
            FETCH NEXT FROM CursorGoal INTO @DiseaseName,@ControlName,@nControlType,@sValue
                END    
    CLOSE CursorGoal
    DEALLOCATE CursorGoal    
    /*----End 治疗目标----*/ 
    
    
    /*----注意事项----*/ 
SET @LastDiseaseName=''
SET @Noticeinfo=''

DECLARE CursorNotice CURSOR
    FOR 
       SELECT C.sName AS DiseaseName,B.sName AS TemplateName,B.nControlType,A.sValue 
  FROM dbo.dia_Pat_NoticeInfo A INNER JOIN dbo.dia_TemplateControl B ON A.TemplateControlID=B.TemplateControlID
  INNER JOIN dbo.dia_DiseaseType C ON B.DiseaseTypeID=C.DiseaseTypeID
   WHERE A.DiagCode=@DiagCode
   GROUP BY c.sName,B.sName,B.nControlType,A.sValue,B.sort
   ORDER BY B.sort
   
   OPEN CursorNotice    
            FETCH NEXT FROM CursorNotice INTO @DiseaseName,@ControlName,@nControlType,@sValue
            WHILE @@FETCH_STATUS=0
                BEGIN
                IF(@LastDiseaseName!=@DiseaseName)
                    BEGIN
                    SET @LastDiseaseName=@DiseaseName
                    SET @Noticeinfo=@Noticeinfo+'#'+@DiseaseName+'$'
                    END                    
                        
                IF(@nControlType='1') 
                    BEGIN
                    SET @TempContent=@ControlName
                    END
                ELSE IF(@nControlType='2') 
                    BEGIN
                    SET @TempContent=@ControlName+'&'+@sValue
                    END
                ELSE IF(@nControlType='3') 
                    BEGIN
                    SET @TempContent=@ControlName+'&'+@sValue
                    END
                    SET @Noticeinfo=@Noticeinfo+'*'+@TempContent                
            FETCH NEXT FROM CursorNotice INTO @DiseaseName,@ControlName,@nControlType,@sValue
                END    
    CLOSE CursorNotice
    DEALLOCATE CursorNotice    
    /*----End 注意事项----*/ 



DECLARE @PEInfo NVARCHAR(4000)
DECLARE @AEInfo NVARCHAR(4000)
DECLARE @EvaluateInfo NVARCHAR(4000)
DECLARE @GoalInfo NVARCHAR(4000)
DECLARE @NoticeInfo NVARCHAR(4000)
EXEC proc_PatCheckInfo '886fa8a5-86f3-4a92-bbb7-fef62c2b2c9a', @PEinfo OUTPUT, @AEinfo OUTPUT,@EvaluateInfo OUTPUT,@GoalInfo OUTPUT,@NoticeInfo OUTPUT
SELECT @PEInfo, @AEInfo,@EvaluateInfo,@GoalInfo,@NoticeInfo

 SqlConnection sqlCon = new SqlConnection(SQLHelper.connectionString);
            SqlCommand sqlCmd = new SqlCommand("proc_PatCheckInfo", sqlCon);
            sqlCmd.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程  

            SqlParameter sqlParme;

            //输入参数 
            sqlParme = sqlCmd.Parameters.Add("@DiagCode", SqlDbType.NVarChar, 50);
            sqlParme.Direction = ParameterDirection.Input;
            sqlParme.Value = "886fa8a5-86f3-4a92-bbb7-fef62c2b2c9a";

            //输出参数 
            sqlParme = sqlCmd.Parameters.Add("@PEInfo", SqlDbType.NVarChar, 4000);
            sqlParme.Direction = ParameterDirection.Output;
            sqlParme = sqlCmd.Parameters.Add("@AEInfo", SqlDbType.NVarChar, 4000);
            sqlParme.Direction = ParameterDirection.Output;
            sqlParme = sqlCmd.Parameters.Add("@EvaluateInfo", SqlDbType.NVarChar, 4000);
            sqlParme.Direction = ParameterDirection.Output;
            sqlParme = sqlCmd.Parameters.Add("@GoalInfo", SqlDbType.NVarChar, 4000);
            sqlParme.Direction = ParameterDirection.Output;
            sqlParme = sqlCmd.Parameters.Add("@NoticeInfo", SqlDbType.NVarChar, 4000);
            sqlParme.Direction = ParameterDirection.Output;

            if (sqlCon.State != ConnectionState.Connecting)
            {
                sqlCon.Open();
            }
            sqlCmd.ExecuteNonQuery();

            string PEInfo = sqlCmd.Parameters[1].Value.ToString();
            string AEInfo = sqlCmd.Parameters[2].Value.ToString();
            string EvaluateInfo = sqlCmd.Parameters[3].Value.ToString();
            string GoalInfo = sqlCmd.Parameters[4].Value.ToString();
            string NoticeInfo = sqlCmd.Parameters[5].Value.ToString();
            if (sqlCon.State != ConnectionState.Closed)
            {
                sqlCon.Close();
            }
            sqlCon.Dispose();