--exec usp_Search 'h'
CREATE Proc usp_Search(
@seacrh varchar(max)=''
)
as
Begin
declare @count int,@id int,@countColumn int=1,@i int=1,@pname varchar(100),@pemail varchar(100),
@cid int,
@aa varchar(200)=''
declare @sql nvarchar(max)=''
set Nocount oN
Select @count=count(*) from TBL_PARENTr where pname like '%'+@seacrh+'%'
CREATE TABLE #Mytemp(sno int identity(1,1),pid int,Pname varchar(100), pEmail varchar(100))
--SET IDENTITY_INSERT #Mytemp on
SET IDENTITY_INSERT TBL_PARENTr on
if(@count>0)
Begin
INSERT INTO #Mytemp(pid,pname,pemail)
SELECT pid,pname,pemail FROM TBL_PARENTr where pname like '%'+@seacrh+'%'
SELECT @countColumn=max(chilcount) FROM TBL_PARENTr where pname like '%'+@seacrh+'%'
select @aa=case when @aa='' then convert(varchar(10),pid) else @aa+','+ convert(varchar(10),pid)end from #Mytemp
--SELECT cid,cname,pid FROM tbl_ChildR where convert(varchar(100), pid) in(1,2,3)
--SELECT cid,cname,pid FROM tbl_ChildR where convert(varchar(100), pid) in('+convert(varchar(100), @aa)+')
--print @aa
SET IDENTITY_INSERT TBL_PARENTr off
--Select @id=pID,@pname=pname,@pemail=pemail from TBL_PARENTr where pname like '%'+@seacrh+'%'
--print @id
--select top 1 @cid=cid from tbl_ChildR where pID=@id
--select @countColumn=count(*) from tbl_ChildR where pid=@id
--Insert into #Mytemp(Pname,pEmail)values(@Pname,@pEmail)
while(@countColumn>0)
Begin
DECLARE @ColName nvarchar(100),@Cname varchar(100)=''
DECLARE @DynamicSQL nvarchar(250),@DynamicSQLupdate nvarchar(max)
--select * from #Mytemp
--print @Cname
set @cid=@cid+1
SET @ColName='CN'+''+convert(varchar(10),@i)
SET @DynamicSQL = 'ALTER TABLE #Mytemp ADD ['+ CAST(@ColName AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'
--print @DynamicSQLupdate
EXEC(@DynamicSQL)
set @countColumn=@countColumn-1
set @i=@i+1
End
declare @row int=1
--set @sql=N'INSERT INTO #MytempChild(cid,cname,pid)SELECT cid,cname,pid FROM tbl_ChildR where convert(varchar(100), pid) in('+convert(varchar(100), @aa)+')'
--execute Sp_executesql @sql
declare @childcount int=0,@j int=1,@pid int
while(@count>0)
Begin
CREATE TABLE #MytempChild(CSno int identity(1,1),cid int,Cname varchar(100),pid int)
select @pid=pid from #Mytemp where sno=@row
select @childcount=count(*) from tbl_ChildR where pid=@pid
INSERT INTO #MytempChild(cid,cname,pid)SELECT cid,cname,pid FROM tbl_ChildR where pid=@pid
set @j=1
while(@childcount>0)
Begin
select @Cname=cname from #MytempChild where CSno=@j
--print '*******'+convert(varchar(20),@row)
SET @DynamicSQLupdate ='Update #Mytemp set CN'+convert(varchar(10),@j)+'='''+convert(varchar(200),@Cname)+''' where sno='+convert(varchar(200), @row)+''
--print @DynamicSQLupdate
EXEC(@DynamicSQLupdate)
set @j=@j+1
set @childcount=@childcount-1
end
drop table #MytempChild
set @count=@count-1
set @row=@row+1
end
select * from #Mytemp
drop table #Mytemp
--SELECT pid,pname,pemail FROM TBL_PARENTr where pname like '%'+@seacrh+'%'
End
End