How to search data in SQL server?

Table- Blog

CREATE TABLE [dbo].[Blog](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](max) NULL,
[ShortDescription] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[ImageUrl] [nvarchar](255) NULL,
[CategoryId] [int] NULL,
[TagKeyWord] [nvarchar](255) NULL,
[SeoTitle] [nvarchar](255) NULL,
[SeoKeyword] [nvarchar](255) NULL,
[SeoDescription] [nvarchar](max) NULL,
[Status] [bit] NULL CONSTRAINT [DF_Blog_Status]  DEFAULT ((0)),
[UserType] [nvarchar](255) NULL,
[UserId] [bigint] NULL,
[CreatedDate] [datetime] NULL CONSTRAINT [DF_Blog_CreatedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Blog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table - BlogCategory
CREATE TABLE [dbo].[BlogCategory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[Name] [varchar](max) NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_BlogCategory_Active]  DEFAULT ((1)),
[seo_keywords] [varchar](255) NULL,
[seo_description] [varchar](255) NULL,
 CONSTRAINT [PK_BlogCategory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO




Create PROC [dbo].[usp_Blog_Search](@SearchName VARCHAR(200))
AS
BEGIN
IF ISNULL(@SearchName,'') = ''
    BEGIN
SELECT b.ID, b.Title, b.ShortDescription, b.Description, b.ImageUrl, b.CategoryId, b.TagKeyWord, b.SeoTitle, b.SeoKeyword, b.SeoDescription,
 b.Status, b.UserType, b.UserId, b.CreatedDate, c.Name,
(case UserType when 'U1' then (select FullName from dbo.UserDetail u where u.ID = b.UserId) 
when 'A1' then (select FirstName + ' ' + LastName from dbo.AdminUser a where a.Id = b.UserId) end) as UserName,
(select COUNT(bv.BlogID) from BlogVisits bv where bv.BlogId=b.ID)  as ViewCount, 
(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID and br.Status=1) as CommentCount,(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID) as CommentCountWithoutStatus,
(case when [Status]=1 then 'Active' when [Status]=0 then 'InActive' end) as StatusActiveDeActive
from Blog b
inner join dbo.BlogCategory c on c.ID= b.CategoryId
where b.[Status]=1  order by b.ID desc
    END
    ELSE
    BEGIN            
SELECT b.ID, b.Title, b.ShortDescription, b.Description, b.ImageUrl, b.CategoryId, b.TagKeyWord, b.SeoTitle, b.SeoKeyword, b.SeoDescription,
 b.Status, b.UserType, b.UserId, b.CreatedDate, c.Name,
(case UserType when 'U1' then (select FullName from dbo.UserDetail u where u.ID = b.UserId) 
when 'A1' then (select FirstName + ' ' + LastName from dbo.AdminUser a where a.Id = b.UserId) end) as UserName,
(select COUNT(bv.BlogID) from BlogVisits bv where bv.BlogId=b.ID)  as ViewCount, 
(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID and br.Status=1) as CommentCount,(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID) as CommentCountWithoutStatus,
(case when [Status]=1 then 'Active' when [Status]=0 then 'InActive' end) as StatusActiveDeActive
from Blog b
inner join dbo.BlogCategory c on c.ID= b.CategoryId
WHERE ((Title LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName))  + '%')
or(TagKeyWord LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName))  + '%')
or(Name LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName))  + '%'))
and [Status]=1 order by ID desc
    END
END