I want to create a stored procedure that Updates from two tables using a paramater (FamilyMemberID).":

See more:

SQL-Server-2008

Thanks a lots....Mr gvprabu sir, You gave the solution in deletion with two tables.... Same problem are coming in Updation the store procedure are write this----
--------

I am new on Sql Server2008 r2 how can update records in two tables with head =true or false condition ???

ALTER PROCEDURE [dbo].[spUpdateParivarRegister]
(
    ---- Fields related to FamilyHead table
    @HouseNo nvarchar(50),
    @StateID int,
    @DistrictID int,
    @WardID int,
    @MohallaID int,     
    
    
    ---- Fields related to FamilyMember table
    @FamilyMemberID int,
    --@SerialNo nvarchar(50),
    @RegistrationStatus Int OUTPUT,
    @ActiveHead bit,
    @MemberName nvarchar(50),
    @FatherHusbandName nvarchar(50),
    
    @RegistrationDate Datetime,
    @DateOfBirth Datetime,
    @Age int,
    @TotalMember int,
    @MobileNo NVarchar(50), 
    @SexID int,
    @MaritalStatusID int,
    @QualificationID int,
    @OccupationID int,
    @ReligionID int,
    @CardId INT,
    @CardNo Nchar(12),
    ---For Image Upload and Saved 
    @PhotoName nvarchar(50), 
    @PhotoImagePath nvarchar(2000),
    
    @GISID nvarchar(50),
    @Language int	
)
AS 
BEGIN
		
Begin Try
	Begin Tran Tran1
		SET @RegistrationStatus = -1
		
		declare @Family int
	BEGIN
				
        	UPDATE	FamilyHead
        	SET		HouseNo = @HouseNo
        		,StateID = @StateID
        		,DistrictID = @DistrictID
        		,WardID = @WardID 
        		,MohallaID = @MohallaID 
        
        		FROM FamilyMember FM 
        		left outer Join FamilyHead FH on FM.FamilyID =FH.FamilyID
          
        --Where FamilyHead.FamilyID=FamilyMember.FamilyID
				 
				
        	if(@ActiveHead=1)
        	BEGIN
        		UPDATE FamilyHead SET FamilyHeadName=@MemberName WHERE FamilyID=@Family
        	END
        	UPDATE FamilyMember 
        	SET     MemberName =@MemberName 
        			,PhotoName=@PhotoName  
        			,PhotoImagePath=@PhotoImagePath
        			--,RegistrationStatus = @RegistrationStatus 
        			--,ActiveHead=@ActiveHead 
        			,FatherHusbandName= @FatherHusbandName
        			,RegistrationDate=@RegistrationDate
        			,DateOfBirth=@DateOfBirth
        			,Age=@Age
        			,TotalMember=@TotalMember 
        			,MobileNo=@MobileNo 
        			,SexID=@SexID
        			,MaritalStatusID=@MaritalStatusID
        			,QualificationID=@QualificationID
        			,OccupationID=@OccupationID 
        			,ReligionID=@ReligionID
        			,CardId=@CardId 
        			,CardNo=@CardNo 
        			,GISID=@GISID
        			,Language=@Language 
        			
        			WHERE FamilyMemberID=@FamilyMemberID
        			
        			--FROM FamilyMember FM 
        	--		left outer Join FamilyHead FH on FM.FamilyID =FH.FamilyID
	
		SET @RegistrationStatus = 1
		
	COMMIT TRAN Tran1
 END	
END TRY
	BEGIN CATCH
	print'Exception Here'
		SET @RegistrationStatus	= 2
		ROLLBACK TRAN Tran1
	END CATCH
END