Simple site search from sql server stored procedures

GO
/****** Object:  StoredProcedure [dbo].[Ameriprise_SP_Get_Data_Search]    Script Date: 08/12/2013 11:24:36 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Create proc [dbo].[SP_Get_Data_Search]
 @SearchText varchar(4000)
as
begin
(
select
ac.MenuCode as pageId,
ac.Displayname as Title,
acd.MetaDescription as [Description],
case when ac.ParentMenuCode=0 and ac.Module=0
then
'L1.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid=0&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
else
case when ac.MenuCode=1 then 'index.aspx' else case when ac.ParentMenuCode<>0 and ac.Module=0
then
'L2.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid='+CAST(ac.ParentMenuCode AS VARCHAR)+'&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
else
case when ac.ParentMenuCode=0 and ac.Module=1
then
'AboutUs.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid='+CAST(ac.ParentMenuCode AS VARCHAR)+'&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
else
case when ac.ParentMenuCode<>0 and ac.Module=1
then
'AU.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid='+CAST(ac.ParentMenuCode AS VARCHAR)+'&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
end
end
end
end
end as [Path],
convert(int,ac.ParentMenuCode) as MatchCount,
convert(decimal,ac.ParentMenuCode) as  Size
from Contentmenu ac inner join ContentDescription acd
on ac.ContentID = acd.ContentID
where ContentHeading like '%'+ @SearchText +'%' or  ContentDescription like '%'+ @SearchText +'%')
 end

GO