Link to home
Start Free TrialLog in
Avatar of shieldguy
shieldguyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Need help in debugging the Error in the T SQL stored procedure

I have stored procedure which is returning the select query result and the maximum rows as an output variable

I am using the paging, but I have set the row numbers per page to 100 which is more than the results appearing on the select
But the now @maximumRows variable is comming more than the select query results

e.g. if @maximumRows = 31
The select query return 29 rows

Thanks
Create PROCEDURE [dbo].[GetReturnProductByQuantity]
 @StartDateAdded as nVarchar(50),
 @EndDateAdded as nVarchar(50),
 @OrderBy as nVarchar(50),
 @isSet as nVarchar(3),
 @CurrentPageIndex as int,
 @totalRowsPerPage as int,
 @maximumRows as int OUTPUT
 
AS
BEGIN
 
	--Decalaring the variables
	Declare @SqlOrderBy as nvarchar(200)
	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	Declare @endRowIndex as int
	Declare @startRowIndex as int
 
	SELECT	@CurrentPageIndex = @CurrentPageIndex - 1
 
 
 
	--Selecting the page index
	If @CurrentPageIndex <= 0 
		Begin 
			Set @startRowIndex = 0
		End
	Else
		Begin
			Set @startRowIndex = @CurrentPageIndex * @totalRowsPerPage
		End
 
	--Selecting the end of row for the page	
	SELECT @endRowIndex = @startRowIndex + (@totalRowsPerPage -1)
	--Selecting the dates 
	SELECT @StartDate = DateAdd(day, 0, @StartDateAdded)       
	SELECT @EndDate = DateAdd(day, +1, @EndDateAdded)       
 
 
 
	--Selecting the order by statement
	If @OrderBy <> ''
		Begin
			SET  @SqlOrderBy = ' Order By ' + Cast(@OrderBy As varchar(50))
		End
    Else
		Begin
			SET  @SqlOrderBy = ' Order By RowNum' 
		End
 
 
 
 
	--Now selecting the maximunRows value in the @maximumRows acoording to the given filter
	IF UPPER(@isSet)='NO'
		BEGIN
			SELECT 
				@maximumRows = IsNull(Count(o.ProductID),0) 
			FROM 
				table1 cs
			LEFT OUTER JOIN 
				table2 o
				   ON o.OrderID = cs.OrderID
			Where
				cs.ReturnReason= 'Faulty Product'
				And cs.DateAdded >= + @StartDate
				And cs.DateAdded < + @EndDate
				And o.QuantityDespatched < 0
				And NOT EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
		END
	ELSE IF UPPER(@isSet)='YES'
		BEGIN
			SELECT 
				@maximumRows = IsNull(Count(o.ProductID),0) 
			FROM 
				table1 cs
			LEFT OUTER JOIN 
				table2 o
				   ON o.OrderID = cs.OrderID
			Where
				cs.ReturnReason= 'Faulty Product'
				And cs.DateAdded >= + @StartDate
				And cs.DateAdded < + @EndDate
				And o.QuantityDespatched < 0
				And EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
		END
 
 
 
 
	--Now selecting the endrows and checking if we are selecting the value by keeping the end of total rows	
	--in mind to avoid errors
	If @endRowIndex >= @maximumRows 
		Begin	
			Set @endRowIndex = @maximumRows 
		End
 
 
 
 
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	 
	--If the value is yes then the query will select all the products except the product set
	IF UPPER(@isSet)='NO'
		BEGIN
			EXEC('Select RowNum, ProductID, Manufacturer, ProductName, ProductSize, ProductColour, QuantityDespatched,ProductDescriptionID
						 From  
							(SELECT 
								o.ProductID ProductID,
								Max(o.Manufacturer) Manufacturer, 
								Max(o.ProductName) ProductName, 
								Max(o.ProductSize) ProductSize, 
								Max(o.ProductColour) ProductColour, 
								Max(o.ProductDescriptionID) ProductDescriptionID,
								Sum(abs(o.QuantityDespatched)) QuantityDespatched,
								ROW_NUMBER() OVER(ORDER BY o.ProductID) as RowNum
							FROM 
								table1 cs
							LEFT OUTER JOIN 
								table2 o
								   ON o.OrderID = cs.OrderID
							Where
								cs.ReturnReason= ''Faulty Product''
								And cs.DateAdded >= ''' + @StartDate + '''
								And cs.DateAdded < ''' + @EndDate + '''
								And o.QuantityDespatched < 0
								And NOT EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
							Group By
							o.ProductID) t1
						WHERE 
					t1.RowNum BETWEEN ' + @startRowIndex + ' AND ' + @endRowIndex + '
				  ' + @SqlOrderBy)
		END
	ELSE IF UPPER(@isSet)='YES'
		BEGIN
			EXEC('Select RowNum, ProductID, Manufacturer, ProductName, ProductSize, ProductColour, QuantityDespatched,ProductDescriptionID
					 From  
						(SELECT 
							o.ProductID ProductID,
							Max(o.Manufacturer) Manufacturer, 
							Max(o.ProductName) ProductName, 
							Max(o.ProductSize) ProductSize, 
							Max(o.ProductColour) ProductColour, 
							Max(o.ProductDescriptionID) ProductDescriptionID,
							Sum(abs(o.QuantityDespatched)) QuantityDespatched,
							ROW_NUMBER() OVER(ORDER BY o.ProductID) as RowNum
						FROM 
							table1 cs
						LEFT OUTER JOIN 
							table2 o
							   ON o.OrderID = cs.OrderID
						Where
							cs.ReturnReason= ''Faulty Product''
							And cs.DateAdded >= ''' + @StartDate + '''
							And cs.DateAdded < ''' + @EndDate + '''
							And o.QuantityDespatched < 0
							And EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
						Group By
						o.ProductID) t1
					WHERE 
				t1.RowNum BETWEEN ' + @startRowIndex + ' AND ' + @endRowIndex + '
			  ' + @SqlOrderBy)
		END
 
END

Open in new window

Avatar of shieldguy
shieldguy
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

One more thing which I like to ask about this stored procedure is that I had tried to implement the Ordre By in it but because I am using the rownumber logic in select the order by only sort the reult of the current page

i hope you undertsand

Thanks
Avatar of Cedric_D
Cedric_D

ROW_NUMBER() function return values started from 1, so you should change code where you set @startRowIndex to be 1-based:

  --Selecting the page index
  If @CurrentPageIndex <= 0 
  Begin 
    Set @startRowIndex = 1
  End
  Else
  Begin
    Set @startRowIndex = @CurrentPageIndex * @totalRowsPerPage + 1
  End

Open in new window

Dont worry about ordering, ROW_NUMBER() OVER(ORDER BY o.ProductID) inside a query will sort on all results.
I didnt understand why you said that

"ROW_NUMBER() function return values started from 1, so you should change code where you set @startRowIndex to be 1-based:"

Please explain

I had made the changes as you have asked but I can still see the @maximumRows  is comming I no. gretaer then rows appearing

thanks
ASKER CERTIFIED SOLUTION
Avatar of Cedric_D
Cedric_D

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
did it work?
> I didnt understand why you said that  "ROW_NUMBER() function return values started from "
> Please explain

ROW_NUMBER() returns values:  1, 2, 3, 4, 5

but your select WHERE RowNum BETWEEN 0 AND 4.

Should be changed to WHERE RowNum BETWEEN 1 AND 5.
oh i see, but one more thing why is the @maximumRows  variable value is 1 time greater then the results appearing, due to this problem I am getting problem at the front end where I am displaying the results on the datagrid and when go to last page and if the last page on suppose to have one record there is no record shown and ist appearing empty.

thanks
ok the stored procedure u give is seem to work fine but except their is one problem the @maximumRows  variable value is not comming up right please check what mistake i m doing

thanks
I noticed your COUNT queries also incorrect.
change
       SELECT @maximumRows = IsNull(Count(o.ProductID),0)
to
       SELECT @maximumRows = Count(distinct o.ProductID)

in two places.
One mote thing the @OrderBy  variable value is comming up like this

e.g. "Manufacturer Desc"
Or
"Manufacturer ASC"


is their any easy logic to attach the

'desc' and 'asc' with code as well
ok, add this:

.......
        --Selecting the dates
        SELECT @StartDate = DateAdd(day, 0, @StartDateAdded)      
        SELECT @EndDate = DateAdd(day, +1, @EndDateAdded)  

declare @dir varchar(10)

set @dir = case when charindex(' DESC', @ORderBy) > 0 then ' DESC' else ' ASC' end
set @OrderBy = rtrim(replace(replace(@OrderBy, ' ASC', ''), ' DESC', ''))

        --Selecting the order by statement
        If @OrderBy <> ''
                Begin
                        SET  @SqlOrderBy = ' Order By ' + case @OrderBy
                                                                              when 'Manufacturer' then 'Max(o.Manufacturer)'
                                                                              when 'ProductName' then 'Max(o.ProductName)'
                                                                              when 'ProductSize' then 'Max(o.ProductSize)'
                                                                              when 'ProductColour' then 'Max(o.ProductColour)'
                                                                              when 'ProductDescriptionID' then 'Max(o.ProductDescriptionID)'
                                                                              when 'QuantityDespatched' then 'Sum(abs(o.QuantityDespatched))'
                                                                              else 'o.ProductID' end
 
                End
    Else
                Begin
                        SET  @SqlOrderBy = ' Order By o.ProductID'
                End
 
set @SqlOrderBy = @SqlOrderBy + @dir
Thanks alot mate