python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
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

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like
www.code-sample.com/. Powered by Blogger.
ASK Questions