asp.net - 如何在 SQL Server 2008 中的单个存储过程中编写两个更新查询

Question

我有一个表，其中包含绑定(bind)到 GridView 的几列。

在该 GridView 中，我有一个编辑选项来更新列。在这种情况下，我需要编写两个更新存储过程，这意味着我选择 AudiotoName 之外的所有列，选择另一列，所有列都会更新以引发一个更新查询，但是当我选择其中具有 的表时>AudiotoName 列，仅编辑以选择该列，它将引发第二个更新存储过程。我尝试过，但它无法正常工作，任何人都可以帮助我。

我的代码:

ALTER PROCEDURE up_file
    (@ModuleID int,
     @SubjectID int,
     @Physician varchar(500) = '',
     @AuditoName varchar(300) = '',
     @AuditoType varchar(50) = '',
     @AudioPath varchar(2000) = '',
     @BaseDocumentName varchar(500) = '',
     @BaseDocumentPath varchar(2000) = '',
     @Createddate datetime, 
     @CreatedBy varchar(200) = '')
AS
BEGIN   
    IF @AuditoName = 'true' //select AuditoName column only raise this update query
    BEGIN
        UPDATE SubjectItems 
        SET ModuleID = @ModuleID,
            SubjectID = @SubjectID,
            Physician = '@Physician',
            AuditoName = '@AuditoName',
            AuditoType = '@AuditoType',
            AudioPath ='@AudioPath',
            BaseDocumentName = '@BaseDocumentName',
            BaseDocumentPath = '@BaseDocumentPath' 
         WHERE AuditoName = @AuditoName
    END
    BEGIN  //normal fields select raise this update query 
       UPDATE SubjectItems 
       SET ModuleID = @ModuleID,
            SubjectID = @SubjectID,
            Physician = '@Physician',
            AuditoName = '@AuditoName',
            AuditoType = '@AuditoType',
            AudioPath ='@AudioPath',
            BaseDocumentName = '@BaseDocumentName',
            BaseDocumentPath = '@BaseDocumentPath' 
         WHERE ModuleID = @ModuleID
    END
END

谁能帮帮我吗？

Answer 1

查询中的问题是，即使 @AuditoName 为 true，较低的更新查询仍在运行。这将重新更新表 SubjectItems。您可以使用 if...else block 代替，如下所示:

ALTER PROCEDURE up_file
    (@ModuleID int,
     @SubjectID int,
     @Physician varchar(500) = '',
     @AuditoName varchar(300) = '',
     @AuditoType varchar(50) = '',
     @AudioPath varchar(2000) = '',
     @BaseDocumentName varchar(500) = '',
     @BaseDocumentPath varchar(2000) = '',
     @Createddate datetime, 
     @CreatedBy varchar(200) = '')
AS
BEGIN   
    IF @AuditoName = 'true' //select AuditoName column only raise this update query
    BEGIN
        UPDATE SubjectItems 
        SET ModuleID = @ModuleID,
            SubjectID = @SubjectID,
            Physician = '@Physician',
            AuditoName = '@AuditoName',
            AuditoType = '@AuditoType',
            AudioPath ='@AudioPath',
            BaseDocumentName = '@BaseDocumentName',
            BaseDocumentPath = '@BaseDocumentPath' 
         WHERE AuditoName = @AuditoName
    END
ELSE
    BEGIN  //normal fields select raise this update query 
       UPDATE SubjectItems 
       SET ModuleID = @ModuleID,
            SubjectID = @SubjectID,
            Physician = '@Physician',
            AuditoName = '@AuditoName',
            AuditoType = '@AuditoType',
            AudioPath ='@AudioPath',
            BaseDocumentName = '@BaseDocumentName',
            BaseDocumentPath = '@BaseDocumentPath' 
         WHERE ModuleID = @ModuleID
    END
END