create procedure PSaleCForms
as
begin
declare
@b varchar(9),
@c nvarchar(500),
@q nvarchar(max)
declare @T table(FY nvarchar(9),Qtr int,title nvarchar (max),invoicenumber nvarchar(max),invoicedate datetime,sp decimal 18,2),grandtotal decimal(18,2))
declare @data cursor
set @data= Cursor
forward_only static
for
select x.DBTitle,y.CurrentFinancialYear from [Accounts Manager].dbo.DBManager x inner join [Accounts Manager].dbo.Accounts y on y.DBID=x.DBID where x.cfy=1
open @data
fetch next from @data
into @c,@b
while @@FETCH_STATUS=0
begin
set @q=N'Select '''+@b+''' [fy], case cast(month(i.invoicedate)/3.1 as int) when 0 then 4 else cast(month(i.invoicedate)/3.1 as int) end [Qtr], l.title,i.invoicenumber,i.invoicedate,i.sp,i.grandtotal from ['+@c+'].dbo.invoicemain i inner join ['+@c+'].dbo.ledgermain l on l.ledgerid=i.ledgerid where (sp=0 or stocktype=''x'') and invoicetype=''DS'''
insert into @T exec [master].dbo.sp_executesql @q fetch next from @data into @c,@b end close @data deallocate @data select * from @T return end