How to write two update requests in one stored procedure in SQL Server 2008

Question

I have a table containing several columns related to gridview.

In this gridview, I have an edit option to update columns. In this situation, I need to write two updated stored procedures that mean that I select all columns waiting for AudiotoName , select other columns, all columns are updated to raise one update request, but when I select a table that has an AudiotoName column, which edits only to select this column, it will raise the second stored update procedure. I tried, but it does not work properly. Can anybody help me.

My code is:

 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?

Answer 1

The problem with your request is that even if @AuditoName true, a lower update request is executed. This will update the SubjectItems table. Instead, you can use the if...else block, as shown below:

 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