How to update two tables in one statement in SQL Server 2008

update two tables in one statement in SQL Server


ALTER PROCEDURE [dbo].[UpdateUserProfile]
(
  @EmailID NVARCHAR(150)
 ,@FirstName NVARCHAR(100)
 ,@LastName NVARCHAR(100)
 ,@Mobile NVARCHAR(15)
 ,@AlternateContact NVARCHAR(15)
 ,@Gender CHAR(1)
 ,@DOB datetime
 ,@Address NVARCHAR(MAX)
 ,@ShortBio NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @REST INT
SET @REST = (SELECT COUNT(*) FROM UserProfile WHERE EmailId =@EmailID)
IF(@REST = 0)   
     INSERT INTO UserProfile(
             EmailId
            ,Gender
            ,DOB
            ,Address
            ,ShortBio
            ,CreateDate
            ,UpdateDate
            ,IsDeleted
   )
   VALUES(
            @EmailID,
            @Gender,
            @DOB,
            @Address,
            @ShortBio,
            GETDATE(),
            GETDATE(),
            0
   ) 

ELSE
  --Update AspNetUsers Table1.
    UPDATE AspNetUsers
    SET  AspNetUsers.FirstName = @FirstName,
             AspNetUsers.LastName = @LastName,
             AspNetUsers.Mobile = @Mobile,
             AspNetUsers.AlternateContact = @AlternateContact
   FROM AspNetUsers T1, UserProfile T2
   WHERE T1.UserName = T2.EmailId AND T2.EmailId =@EmailID
  
      --Update UserProfile Table2.
    UPDATE UserProfile
     SET UserProfile.Gender = @Gender,
             UserProfile.DOB = @DOB,
             UserProfile.Address = @Address,
             UserProfile.ShortBio = @ShortBio,
             UserProfile.UpdateDate = GETDATE()
   FROM AspNetUsers T1, UserProfile T2
   WHERE T1.UserName = T2.EmailId AND T2.EmailId =@EmailID  

END


ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

update two tables in one statement in SQL Server update two tables in one statement in SQL Server Reviewed by Anil Singh on 5:31 AM Rating: (5)
www.code-sample.com/. Powered by Blogger.
^