How to make sub queries with success with multiple parameter-CodePudding

i have to table PA_Ledger and PA_Trans. i just made one stored procedure that filters with date,inner join ,group by PA_Ledger.Name and Sort with that Name.

i have one stored procedure that finds records with join,group by and date filter as like :

USE [Pact]
GO
/****** Object:  StoredProcedure [dbo].[PA_Report_IndexPagingData]    Script Date: 11/01/2021 10:41:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Shalin Gajjar
-- Create date: 27/10/221
-- Description: get all report data
-- =============================================
--exec [dbo].[PA_Report_IndexPagingData] 100,1,'Oct  28 2021 12:00AM','Oct  30 2021 12:00AM','Order By Name Asc',''
ALTER PROCEDURE [dbo].[PA_Report_IndexPagingData]
    --10,1,'Order By Name Asc','',3
    -- Add the parameters for the stored procedure here
    @PageSize int,
    @PageIndex int,
    @startdt1 nvarchar(50),  
    @startdt2 nvarchar(50),
    @Sort nvarchar(50),
    @Search nvarchar(max)
AS


BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @qry nvarchar(max)
    declare @TotalRecords nvarchar(max)



    set @TotalRecords=' ( select count(*) from PA_Ledger pl join PA_Trans pt on (pl.LedgerId = pt.Ledgeridcr or pl.LedgerId = pt.Ledgeriddr ) 
                         where isnull(Isdeleted,0) = 0 and pl.Name like ''%' @Search '%'' 
                          and (pt.TransOn >= ''' convert(nvarchar,CONVERT(DATETIME,@startdt1,110)) ''' or convert(date,''' convert(nvarchar,CONVERT(DATETIME,@startdt1,110)) ''') = convert(date,''01/01/1900'') )
                         and (pt.TransOn <=''' convert(nvarchar,CONVERT(DATETIME,@startdt2,110)) ''' or convert(date,''' convert(nvarchar,CONVERT(DATETIME,@startdt2,110)) ''') = convert(date,''01/01/1900'') )
                         group by pl.Name)'
    

    
    set @qry = ' select  *, ' @TotalRecords ' As TotalRecords,                  
                        (select row_number() over(order by pl.name) as RowNo,pl.Name,sum(pt.Amount) as Ammount
                         from PA_Ledger pl
                         join PA_Trans pt on (pl.LedgerId = pt.Ledgeridcr or pl.LedgerId = pt.Ledgeriddr)
                         and pl.Name like ''%' @Search '%'' and
                         isnull(Isdeleted,0) = 0 
                         and (pt.TransOn >= ''' convert(nvarchar,CONVERT(DATETIME,@startdt1,110)) ''' or convert(date,''' convert(nvarchar,CONVERT(DATETIME,@startdt1,110)) ''') = convert(date,''01/01/1900'') )
                         and (pt.TransOn <=''' convert(nvarchar,CONVERT(DATETIME,@startdt2,110)) ''' or convert(date,''' convert(nvarchar,CONVERT(DATETIME,@startdt2,110)) ''') = convert(date,''01/01/1900'') )
                         group by Name 
                        ) i where RowNo between  ' Convert(nvarchar(10),( (@pageIndex-1) * @pageSize  )   1 )  ' and '    Convert(varchar(10),( (@pageIndex-1) * @pageSize  )   @pageSize)   ' '  @Sort

    
    print(@qry)             
    exec(@qry @TotalRecords)
    
END

when execute then generate error like :

( select count(*) from PA_Ledger pl join PA_Trans pt on (pl.LedgerId = pt.Ledgeridcr or pl.LedgerId = pt.Ledgeriddr ) 
                         where isnull(Isdeleted,0) = 0 and pl.Name like '%%' 
                          and (pt.TransOn >= 'Oct 28 2021 12:00AM' or convert(date,'Oct 28 2021 12:00AM') = convert(date,'01/01/1900') )
                         and (pt.TransOn <='Oct 30 2021 12:00AM' or convert(date,'Oct 30 2021 12:00AM') = convert(date,'01/01/1900') )
                         group by pl.Name)
 select  *,  ( select count(*) from PA_Ledger pl join PA_Trans pt on (pl.LedgerId = pt.Ledgeridcr or pl.LedgerId = pt.Ledgeriddr ) 
                         where isnull(Isdeleted,0) = 0 and pl.Name like '%%' 
                          and (pt.TransOn >= 'Oct 28 2021 12:00AM' or convert(date,'Oct 28 2021 12:00AM') = convert(date,'01/01/1900') )
                         and (pt.TransOn <='Oct 30 2021 12:00AM' or convert(date,'Oct 30 2021 12:00AM') = convert(date,'01/01/1900') )
                         group by pl.Name) As TotalRecords,
                        
                        (select row_number() over(order by pl.name) as RowNo,pl.Name,sum(pt.Amount) as Ammount
                         from PA_Ledger pl
                         join PA_Trans pt on (pl.LedgerId = pt.Ledgeridcr or pl.LedgerId = pt.Ledgeriddr)
                         and pl.Name like '%%' and
                         isnull(Isdeleted,0) = 0 
                         and (pt.TransOn >= 'Oct 28 2021 12:00AM' or convert(date,'Oct 28 2021 12:00AM') = convert(date,'01/01/1900') )
                         and (pt.TransOn <='Oct 30 2021 12:00AM' or convert(date,'Oct 30 2021 12:00AM') = convert(date,'01/01/1900') )
                         group by Name 
                        ) i where RowNo between  1 and 100 Order By Name Asc
Msg 207, Level 16, State 1, Line 27
Invalid column name 'RowNo'.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'RowNo'.
Msg 263, Level 16, State 1, Line 13
Must specify table to select from.
Msg 116, Level 16, State 1, Line 27
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'Name'.

what is the better solution for this stored proc.. please help me to out of this stucks..

CodePudding user response：

Well, you haven't given us create/insert queries, so we have to go blind. Try changing the first line of your set @qry:

set @qry = ' select  *, ' @TotalRecords ' As TotalRecords,

to this:

set @qry = ' select  *, ' @TotalRecords ' As TotalRecords, i.* from

This will cause your parenthesis (...) i to be a derived table instead of a single value, and we SELECT all its elements , i.*

Also, change @totalrecords to this version with alternate aliases:

set @TotalRecords=' ( select count(*) from PA_Ledger pl2 join PA_Trans pt2 on (pl2.LedgerId = pt2.Ledgeridcr or pl2.LedgerId = pt2.Ledgeriddr ) 
                         where isnull(Isdeleted,0) = 0 and pl2.Name like ''%' @Search '%'' 
                          and (pt2.TransOn >= ''' convert(nvarchar,CONVERT(DATETIME,@startdt1,110)) ''' or convert(date,''' convert(nvarchar,CONVERT(DATETIME,@startdt1,110)) ''') = convert(date,''01/01/1900'') )
                         and (pt2.TransOn <=''' convert(nvarchar,CONVERT(DATETIME,@startdt2,110)) ''' or convert(date,''' convert(nvarchar,CONVERT(DATETIME,@startdt2,110)) ''') = convert(date,''01/01/1900'') )
                         group by pl2.Name)'