Stored procedure

AmazonListing

USE [amz_reviews]
GO

/****** Object:  StoredProcedure [dbo].[sp_AmazonListing]    Script Date: 8/25/2020 12:01:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_AmazonListing]  
@PageNumber int,
@RowsPerPage int,
@SortColumn NVARCHAR(30),
@SortDirection NVARCHAR(10),
@SearchWord NVARCHAR(150),
@Channel VARCHAR(20),
@Status int
AS
BEGIN
	select count(*) as Total
	from [amz_reviews].[dbo].[AmzListing] el
	where 
	(
	    (el.Sku Like '%'+@SearchWord+'%'
		OR
		@SearchWord IS NULL Or @SearchWord = '')
		and
		(el.ChannelCode=@Channel OR @Channel IS NULL or @Channel='')	
		and
		(el.Status=@Status OR @Status IS NULL or @Status=2)	
	)

	select el.Id,el.Title,el.Sku,el.Price,el.ChannelCode,el.Status,el.Url
	from AmzListing el
	where 
	(
	    (el.Sku Like '%'+@SearchWord+'%'
		OR
		@SearchWord IS NULL Or @SearchWord = '')
		and
		(el.ChannelCode=@Channel OR @Channel IS NULL or @Channel='')	
		and
		(el.Status=@Status OR @Status IS NULL or @Status=2)
	)
    
	Order BY
	CASE WHEN @SortColumn = 0 AND @SortDirection = 'asc' THEN  el.Title  END ASC,
	CASE WHEN @SortColumn = 0 AND @SortDirection = 'desc' THEN  el.Title  END DESC,
	CASE WHEN @SortColumn = 1 AND @SortDirection = 'asc' THEN  el.SKU  END ASC,
	CASE WHEN @SortColumn = 1 AND @SortDirection = 'desc' THEN  el.SKU  END DESC,
	CASE WHEN @SortColumn = 2 AND @SortDirection = 'asc' THEN  el.Price  END ASC,
	CASE WHEN @SortColumn = 2 AND @SortDirection = 'desc' THEN  el.Price  END DESC,
	CASE WHEN @SortColumn = 3 AND @SortDirection = 'asc' THEN  el.ChannelCode  END ASC,
	CASE WHEN @SortColumn = 3 AND @SortDirection = 'desc' THEN  el.ChannelCode  END DESC,
	CASE WHEN @SortColumn = 4 AND @SortDirection = 'asc' THEN  el.Status  END ASC,
	CASE WHEN @SortColumn = 4 AND @SortDirection = 'desc' THEN  el.Status  END DESC
	OFFSET 
	 case when @rowsPerPage = -1
	 then
		0
     else
		((@pageNumber - 1) * @rowsPerPage) 
	 end ROWS
	 
	 FETCH NEXT 

	 case when @rowsPerPage = -1
	 then
		2147483647
     else
		@rowsPerPage 
	 end	
		ROWS ONLY

END








GO

status_update

USE [amz_reviews]
GO

/****** Object:  StoredProcedure [dbo].[status_update]    Script Date: 8/25/2020 12:03:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[status_update] @status int,@id int,@table_name nvarchar(50),@column nvarchar(50)
AS
	declare @query nvarchar(100)='UPDATE '+@table_name+' SET '+@column+'='+CONVERT ( nvarchar(10) , @status)   +' WHERE ID='+CONVERT ( nvarchar(20) , @id);
BEGIN
	exec(@query)
END
GO

sp_BotExceptionLog

USE [amz_reviews]
GO

/****** Object:  StoredProcedure [dbo].[sp_BotExceptionLog]    Script Date: 8/25/2020 12:06:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_BotExceptionLog]  
@PageNumber int,
@RowsPerPage int,
@SortColumn NVARCHAR(30),
@SortDirection NVARCHAR(10),
@SearchWord NVARCHAR(150),
@Channel VARCHAR(20),
@BotName VARCHAR(30)
AS
BEGIN
	select count(*) as Total
	from [amz_reviews].[dbo].[BotExceptionLog] el
	where 
	(
	    (el.Exception Like '%'+@SearchWord+'%'
		OR
		@SearchWord IS NULL Or @SearchWord = '')
		and
		(el.Channel=@Channel OR @Channel IS NULL or @Channel='')	
		and
		(el.Bot=@BotName OR @BotName IS NULL or @BotName='')	
	)

	select el.Id,el.Exception,el.Channel,el.Bot,el.CreatedByDate
	from BotExceptionLog el
	where 
	(
	    (el.Exception Like '%'+@SearchWord+'%'
		OR
		@SearchWord IS NULL Or @SearchWord = '')
		and
		(el.Channel=@Channel OR @Channel IS NULL or @Channel='')	
		and
		(el.Bot=@BotName OR @BotName IS NULL or @BotName='')
	)
    
	Order BY
	CASE WHEN @SortColumn = 1 AND @SortDirection = 'asc' THEN  el.Exception  END ASC,
	CASE WHEN @SortColumn = 1 AND @SortDirection = 'desc' THEN  el.Exception  END DESC,	
	CASE WHEN @SortColumn = 2 AND @SortDirection = 'asc' THEN  el.Channel  END ASC,
	CASE WHEN @SortColumn = 2 AND @SortDirection = 'desc' THEN  el.Channel  END DESC,
	CASE WHEN @SortColumn = 3 AND @SortDirection = 'asc' THEN  el.Bot  END ASC,
	CASE WHEN @SortColumn = 3 AND @SortDirection = 'desc' THEN  el.Bot  END DESC,
	CASE WHEN @SortColumn = 4 AND @SortDirection = 'asc' THEN  el.CreatedByDate  END ASC,
	CASE WHEN @SortColumn = 4 AND @SortDirection = 'desc' THEN  el.CreatedByDate  END DESC
	OFFSET 
	 case when @rowsPerPage = -1
	 then
		0
     else
		((@pageNumber - 1) * @rowsPerPage) 
	 end ROWS
	 
	 FETCH NEXT 

	 case when @rowsPerPage = -1
	 then
		2147483647
     else
		@rowsPerPage 
	 end	
		ROWS ONLY

END








GO