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

I have a table that contains a few columns bound to a gridview.

我有一个表，其中包含绑定到gridview的几列。

In that gridview, I have an edit option to update the columns. In that situation I need to write a two update stored procedures that means I select all columns expect AudiotoName, select another columns all columns are update to raise one update query but when I select table in that have AudiotoName column that only edit to select that column it will raise second update stored procedure. I tried but it not properly working can anyone help me out.

在该gridview中，我有一个编辑选项来更新列。在那种情况下，我需要写一个两个更新存储过程，这意味着我选择所有列期望AudiotoName，选择另一列所有列都更新以提出一个更新查询，但当我选择表中有AudiotoName列，只编辑选择该列它将引发第二次更新存储过程。我试过但是没有正常工作可以任何人帮助我。

My code:

我的代码：

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

Can anyone help me out?

谁能帮我吗？

1 个解决方案

#1

The problem in your query is that, even if @AuditoName is true, the lower update query is running. This will re-update the table SubjectItems. You can use if...else block instead, like below:

您的查询中的问题是，即使@AuditoName为true，也会运行较低的更新查询。这将重新更新表SubjectItems。您可以使用if ... else块，如下所示：

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