我必須列出 PA_Ledger 和 PA_Trans。我剛剛制作了一個存盤程序，它使用日期、內部連接、按 PA_Ledger.Name 分組并使用該名稱進行排序。
我有一個存盤程序，它使用連接、分組依據和日期過濾器查找記錄，如下所示：
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
執行時會產生如下錯誤：
( 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'.
這個存盤程序的更好解決方案是什么..請幫我解決這個問題..
uj5u.com熱心網友回復：
好吧，你還沒有給我們創建/插入查詢，所以我們必須盲目。嘗試更改您的第一行set @qry：
set @qry = ' select *, ' @TotalRecords ' As TotalRecords,
對此：
set @qry = ' select *, ' @TotalRecords ' As TotalRecords, i.* from
這將導致您的括號(...) i成為派生表而不是單個值，我們選擇它的所有元素, i.*
另外，使用備用別名將@totalrecords 更改為此版本：
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)'
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/343698.html
上一篇：如何在IIS上發布WCFWeb服務（在CodeGearDelphifor.NET中創建）？
下一篇：根據優先級加入和獲取列