/* Creating Parent and Child Table description */

/* Parent Table */

create table TSTMP01User(
 MP01Key int identity(1,1) not null,
 UserName varchar(250) not null,
 PassWord varchar(25) not null,
 MP02Role int not null, 
CRDateTime DateTime not null,
LUDateTime DateTime not null,
MP01Status int not null ,
constraint TSTMP01User_PK_MP01Key Primary Key (MP01Key),
constraint TSTMP01User_CK_MP01Status check (MP01Status=0 or MP01Status=1)
);

/* Child Table */

 create table TSTMC01User (
 MC01Key int identity(1,1) not null,
 MP01UserKey int not null,
 UserFullName varchar(100) not null,
fatherName varchar(100) null,
MotherName varchar(100) null,
UserAddress varchar(500) not null,
CRDateTime DateTime not null,
LUDateTime DateTime not null,
MC01Status int not null,
constraint TSTMC01User_PK_MC01Key Primary Key (MC01Key),
constraint TSTMC01User_FK_MP01UserKey Foreign Key (MP01Userkey) References TSTMP01User (MP01Key),
constraint TSTMC01User_CK_MC01Status check (MC01Status=0 or MC01Status=1)
);

/* Insert Script For Parent Table*/

insert into TSTMP01User (UserName,PassWord,MP02Role,CRDateTime,LUDateTime,MP01Status) 
values ('Raman','Raman456',0,getdate(),getdate(),0);

insert into TSTMP01User (UserName,PassWord,MP02Role,CRDateTime,LUDateTime,MP01Status) 
values ('Doyensys','Doyensys123',1,getdate(),getdate(),0);

insert into TSTMP01User (UserName,PassWord,MP02Role,CRDateTime,LUDateTime,MP01Status) 
values ('Delight','Customer123',2,getdate(),getdate(),0);



/* Insert Script For Child Table*/

insert into TSTMC01User (MP01UserKey,UserFullName,fatherName,MotherName,UserAddress,CRDateTime,LUDateTime,MC01Status) 
values (1,'Raman Kumaran','Kumaran','vijaya','trichy',getdate(),getdate(),0),
(2,'Doyensys one','Doyensys Father','Doyensys mother','chennai',getdate(),getdate(),0);

insert into TSTMC01User (MP01UserKey,UserFullName,fatherName,MotherName,UserAddress,CRDateTime,LUDateTime,MC01Status) 
values(3,'Delight Customer','NA','NA','US',getdate(),getdate(),0);


select * from TSTMP01User;
select * from TSTMC01User;


/* Calling Stored Procedure */ 

Declare @RecordCount bigint ;
exec sp_common_search
  @ScreenName='Frm_M_User',       
  @Page ='1',
  @SearchBy ='UserName',        
  @SearchValue ='ra',      
  @RecordCount = @RecordCount output
  select @RecordCount  as totalrecordcount;
  
  
  Declare @RecordCount bigint ;
exec sp_common_search
  @ScreenName='Frm_M_User',       
  @Page ='1',
  @SearchBy ='UserName',        
  @SearchValue ='doyen',      
  @RecordCount = @RecordCount output
  select @RecordCount  as totalrecordcount;
  
  Declare @RecordCount bigint ;
exec sp_common_search
  @ScreenName='Frm_M_User',       
  @Page ='1',
  @SearchBy ='UserAddress',        
  @SearchValue ='%i%',      
  @RecordCount = @RecordCount output
  select @RecordCount  as totalrecordcount;