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

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Buy Books Online at Best Prices

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.