Createing a SP for Update Page using multiple condition and store data into temp table

-- =============================================                                                                                        
-- Author:  Jitendra singh                                                                                        
-- Create date: 05/06/2013                                                                                        
-- Description: get the user text,pic,music,video friend details shared                                                                                        
-- =============================================                                                                                          
ALTER PROCEDURE  lsp_GetUpdShredModule                                                                  
@u_Id BIGINT ,                                                                  
@max_RowIndex int,                                        
@min_RowIndex int                                

AS                                                                                        

BEGIN  

DECLARE @temp1 TABLE  
(                    
[id] int IDENTITY(1,1) NOT NULL,  
[usrid] [bigint] NULL,  
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,    
[usrimage] [nvarchar](500)NULL,  
[MdlTypId] [tinyint] NULL,                                                                                        
[stypeid] [int] NULL,    
[stype] [nvarchar](50) NULL,  
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,  
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,  
[Likests] [int] NULL,  
[DisLikeCnt] [int] NULL,  
[DisLikests] [int] NULL,  
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,      
[cdate] [datetime] NULL                                                                                        

)        

DECLARE @temp2 TABLE  
(                    
[id] int IDENTITY(1,1) NOT NULL,  
[usrid] [bigint] NULL,  
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,    
[usrimage] [nvarchar](500)NULL,  
[MdlTypId] [tinyint] NULL,                                                                                        
[stypeid] [int] NULL,    
[stype] [nvarchar](50) NULL,  
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,  
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,  
[Likests] [int] NULL,  
[DisLikeCnt] [int] NULL,  
[DisLikests] [int] NULL,  
[cmntCnt] [int] NULL,  
[AlbmImgCnt] [int] NULL,  
[cdate] [datetime] NULL                                                                                        

)            

--Insert comment share in temp talbe                                                                            
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,MdlTypId,stypeId,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)                                                                                        
SELECT  distinct(ust.usrid) as usrid,(ust.usrShrTxtId) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,                                                                                        
(SELECT dbo.GetUsrImgPath(ust.usrid))  as usrimage,                                                                                        
(ust.txtDtl) as sDtl,1 as MdlTypId,(ust.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(ust.usrShrTxtId,1)) As LikeCnt,    
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=1 and tls.usrShrid=ust.usrShrTxtId and tls.usrid=@u_Id) as Likests,                              

(dbo.fn_DisLikeCnt(ust.usrShrTxtId,1)) As DisLikeCnt,    
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=1 and tds.usrShrid=ust.usrShrTxtId and tds.usrid=@u_Id) as DisLikests,                        

(dbo.fn_CmntCnt(ust.usrShrTxtId,1)) As cmntCnt, ust.cdate FROM tblUsrShrTxt ust                                                                                        
INNER JOIN tblUsrShrSubTypStatic uscts ON ust.ShrComnTypId=uscts.UsrShrSubTypStaticId                                                                                        
INNER JOIN tblusr usr ON ust.usrid=usr.usrid                                                                                        
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id                                                                                        

--Insert Picture share in temp talbe                                                                    
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)                                                                    
SELECT  distinct(usp.usrid) as usrid,(usp.usrShrPicid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,                                                                                        
(SELECT dbo.GetUsrImgPath(usp.usrid)) as usrimage, (usp.picDtl) as sDtl,
('UserFiles/100/'+cast(usp.usrid as varchar)+'/'+'SharedImages/' + +cast(usp.usrShrPicid as varchar)+ '.'+ substring(usp.picUrl,charindex('.', usp.picUrl)+1,len(usp.picUrl))) as sUrl,                    
2 as MdlTypId,(usp.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(usp.usrShrPicid,2)) As LikeCnt,    
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=2 and tls.usrShrid=usp.usrShrPicid and tls.usrid=@u_Id) as Likests,                              

(dbo.fn_DisLikeCnt(usp.usrShrPicid,2)) As DisLikeCnt,    
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=2 and tds.usrShrid=usp.usrShrPicid and tds.usrid=@u_Id) as DisLikests,                        

(dbo.fn_CmntCnt(usp.usrShrPicid,2)) As cmntCnt,usp.cdate FROM tblUsrShrPic usp                                                  
INNER JOIN tblUsrShrSubTypStatic uscts ON usp.ShrComnTypId=uscts.UsrShrSubTypStaticId                                                                                        
INNER JOIN tblusr usr ON usp.usrid=usr.usrid                                                                                        
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id                                                                                        
 

--Insert Album share in temp talbe                                                                                        
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,title,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)                                                                                        
SELECT distinct(usAlb.usrid) as usrid,(usAlb.UsrShrAlbmId) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,                                                  
(SELECT dbo.GetUsrImgPath(usAlb.usrid))  as usrimage,(usAlb.albumdesc) as sDtl,(usAlb.titlImg) as sUrl,3 as MdlTypId,
(usAlb.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,(usAlb.albumname) as title,

(dbo.fn_LikeCnt(usAlb.UsrShrAlbmId,3)) As LikeCnt,    
(select tls.sts from tblUsrLike tls where tls.shrtypId=3 and tls.usrShrid=usAlb.UsrShrAlbmId and tls.usrid=@u_Id) as Likests,                              

(dbo.fn_DisLikeCnt(usAlb.UsrShrAlbmId,3)) As DisLikeCnt,    
(select tds.sts from tblUsrDislike tds where tds.shrtypId=3 and tds.usrShrid=usAlb.UsrShrAlbmId and tds.usrid=@u_Id) as DisLikests,                        

(dbo.fn_CmntCnt(usAlb.UsrShrAlbmId,3)) As cmntCnt,

(dbo.fn_AlbmImgCnt(usAlb.UsrShrAlbmId,usAlb.usrid)) As AlbmImgCnt,usAlb.cdate FROM tblUsrShrAlbm usAlb
                                                               
INNER JOIN tblUsrShrSubTypStatic uscts ON usAlb.ShrComnTypId=uscts.UsrShrSubTypStaticId                                                                                        
INNER JOIN tblusr usr ON usAlb.usrid=usr.usrid                                                                                        
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id                                                                                        

--Insert Video share in temp talbe

INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usv.usrid) as usrid,(usv.usrShrVidid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usv.usrid))  as usrimage,    
(usv.vidDtl) as sDtl,(usv.vidUrl) as sUrl,4 as MdlTypId,(usv.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(usv.usrShrVidid,4)) As LikeCnt,    
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=4 and tls.usrShrid=usv.usrShrVidid and tls.usrid=@u_Id) as Likests,                              

(dbo.fn_DisLikeCnt(usv.usrShrVidid,4)) As DisLikeCnt,    
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=4 and tds.usrShrid=usv.usrShrVidid and tds.usrid=@u_Id) as DisLikests,                        

(dbo.fn_CmntCnt(usv.usrShrVidid,4)) As cmntCnt,usv.cdate FROM tblUsrShrVid usv  
                                                             
INNER JOIN tblUsrShrSubTypStatic uscts ON usv.ShrComnTypId=uscts.UsrShrSubTypStaticId                                                                                        
INNER JOIN tblusr usr ON usv.usrid=usr.usrid                                                                                        
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id                              


--Insert file share in temp talbe                              

INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,title,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)  
SELECT distinct(usd.usrid) as usrid,(usd.usrShrfdataid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usd.usrid))  as usrimage,(usd.DataDtl) as sDtl,
('UserFiles/100/'+cast(usd.usrid as varchar)+'/'+'Data/' + +cast(usd.usrShrfdataid as varchar)+ '.'+ substring(usd.fname,charindex('.', usd.fname)+1,len(usd.fname))) as sUrl,  
(usd.fname) as title,  6 as MdlTypId,(usd.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(usd.usrShrfdataid,6)) As LikeCnt,    
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=6 and tls.usrShrid=usd.usrShrfdataid and tls.usrid=@u_Id) as Likests,                              

(dbo.fn_DisLikeCnt(usd.usrShrfdataid,6)) As DisLikeCnt,    
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=6 and tds.usrShrid=usd.usrShrfdataid and tds.usrid=@u_Id) as DisLikests,                        

(dbo.fn_CmntCnt(usd.usrShrfdataid,6)) As cmntCnt,usd.cdate FROM tblUsrShrfileData usd      
INNER JOIN tblUsrShrSubTypStatic uscts ON usd.ShrComnTypId=uscts.UsrShrSubTypStaticId  
INNER JOIN tblusr usr ON usd.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id                            


--New Change for scroll using last posted data          
INSERT INTO @temp2(usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate FROM @temp1 ORDER BY cdate desc    


SELECT  id,usrid,shrid,usrname,usrimage,MdlTypId,stypeid,stype,title,sDtl,sUrl,Level1,Level2,Level3,Level4,Level5,LikeCnt,Likests,DisLikeCnt,DisLikests,
cmntCnt,AlbmImgCnt,CONVERT(VARCHAR(25),cdate,100) as cdate  FROM @temp2          
WHERE id BETWEEN @min_RowIndex and @max_RowIndex  order by id desc



END                                                                                        


--exec lsp_GetUpdShredModule 100,500,0  
   
--select * from tblUsrShrfileData

--select * from tblUsrShrAlbm

--select * from tblUsrShrAlbmImg