这个存储过程怎么改?在线等待
CREATE PROCEDURE [dbo].[T_spImportK3Account]
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
AS
SELECT v.FVoucherID, convert(char(10),v.FDate,121) FDate,v.Fyear,v.Fperiod,v.FNumber,v.FEntryCount,v.FDebitTotal, v.FChecked,
v.FPreparerID,u1.FName as FPreparer,v.FCheckerID,u2.FName as FChecker,e.FAccountID, a.FNumber FAccountNumber,
a.FName as FAccountName, e.FDetailID,e.FDC, e.FAmount, e.FQuantity, e.FUnitPrice, e.FExplanation,e.FEntryID into #temp1
FROM @FBrnoName.. t_Voucher v With (Readpast) INNER JOIN @FBrnoName ..t_VoucherEntry e
WITH (Readpast) ON v.FVoucherID=e.FVoucherID INNER JOIN @FBrnoName ..t_Account a ON e.FAccountID=a.FAccountID
Left outer join @FBrnoName .. t_Account a1 ON e.FAccountID2=a1.FAccountID
Left outer join @FBrnoName ..t_User u1 on v.FPreparerID = u1.FUserID
Left outer join @FBrnoName ..t_User u2 on v.FCheckerID = u2.FUserID
WHERE v.Fdate > =@Fdate1 And v.Fdate <= @Fdate2 AND
a.Fnumber > = @FAccountNumber1 And a.Fnumber <= @FAccountNumber2
GO
[解决办法]
CREATE PROCEDURE [dbo].[T_spImportK3Account]
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
AS
BEGIN
EXEC( 'SELECT v.FVoucherID, convert(char(10),v.FDate,121) FDate,v.Fyear,v.Fperiod,v.FNumber,v.FEntryCount,v.FDebitTotal, v.FChecked,
v.FPreparerID,u1.FName as FPreparer,v.FCheckerID,u2.FName as FChecker,e.FAccountID, a.FNumber FAccountNumber,
a.FName as FAccountName, e.FDetailID,e.FDC, e.FAmount, e.FQuantity, e.FUnitPrice, e.FExplanation,e.FEntryID into #temp1
FROM '+@FBrnoName+ '..t_Voucher v With (Readpast)
INNER JOIN '+@FBrnoName+ '..t_VoucherEntry e WITH (Readpast) ON v.FVoucherID=e.FVoucherID
INNER JOIN '+@FBrnoName+ ' ..t_Account a ON e.FAccountID=a.FAccountID
Left outer join '+@FBrnoName+ '.. t_Account a1 ON e.FAccountID2=a1.FAccountID
Left outer join '+@FBrnoName+ '..t_User u1 on v.FPreparerID = u1.FUserID
Left outer join '+@FBrnoName+ '..t_User u2 on v.FCheckerID = u2.FUserID
WHERE v.Fdate > = ' ' '+@Fdate1+ ' ' ' And v.Fdate <= ' ' '+@Fdate2+ ' ' ' AND
a.Fnumber > = ' ' '+@FAccountNumber1+ ' ' ' And a.Fnumber <= ' ' '+@FAccountNumber2+ ' ' ' ')
END
GO
[解决办法]
CREATE PROCEDURE [dbo].[T_spImportK3Account]
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
AS
declare @sql varchar(8000)
set @sql = 'SELECT v.FVoucherID, convert(char(10),v.FDate,121) FDate,v.Fyear,v.Fperiod,v.FNumber,v.FEntryCount,v.FDebitTotal, v.FChecked,v.FPreparerID,u1.FName as FPreparer,v.FCheckerID,u2.FName as FChecker,e.FAccountID, a.FNumber FAccountNumber, a.FName as FAccountName, e.FDetailID,e.FDC, e.FAmount, e.FQuantity, e.FUnitPrice, e.FExplanation,e.FEntryID into #temp1 FROM '+@FBrnoName+ '.. t_Voucher v With (Readpast) INNER JOIN '+@FBrnoName + '..t_VoucherEntry e WITH (Readpast) ON v.FVoucherID=e.FVoucherID INNER JOIN '+@FBrnoName+ '..t_Account a ON e.FAccountID=a.FAccountID Left outer join '+@FBrnoName + '.. t_Account a1 ON e.FAccountID2=a1.FAccountID Left outer join '+@FBrnoName + '..t_User u1 on v.FPreparerID = u1.FUserID Left outer join '+@FBrnoName + '..t_User u2 on v.FCheckerID = u2.FUserID WHERE v.Fdate > = '+@Fdate1+ ' And v.Fdate <= '+@Fdate2+ ' AND a.Fnumber > = '+@FAccountNumber1+ ' And a.Fnumber <= '+@FAccountNumber2
EXEC sp_executesql @Sql
[解决办法]
CREATE PROCEDURE [dbo].[T_spImportK3Account]
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
AS
declare @str varchar(8000)
set @str = 'SELECT v.FVoucherID, convert(char(10),v.FDate,121) FDate,v.Fyear,v.Fperiod,v.FNumber,v.FEntryCount,v.FDebitTotal, v.FChecked,
v.FPreparerID,u1.FName as FPreparer,v.FCheckerID,u2.FName as FChecker,e.FAccountID, a.FNumber FAccountNumber,
a.FName as FAccountName, e.FDetailID,e.FDC, e.FAmount, e.FQuantity, e.FUnitPrice, e.FExplanation,e.FEntryID into #temp1
FROM '+@FBrnoName+ '.. t_Voucher v With (Readpast) INNER JOIN '+@FBrnoName+ '..t_VoucherEntry e
WITH (Readpast) ON v.FVoucherID=e.FVoucherID INNER JOIN '+@FBrnoName+ '..t_Account a ON e.FAccountID=a.FAccountID
Left outer join '+@FBrnoName+ '.. t_Account a1 ON e.FAccountID2=a1.FAccountID
Left outer join '+@FBrnoName+ '..t_User u1 on v.FPreparerID = u1.FUserID
Left outer join '+@FBrnoName+ '..t_User u2 on v.FCheckerID = u2.FUserID
WHERE v.Fdate > =@Fdate1 And v.Fdate <= @Fdate2 AND
a.Fnumber > = @FAccountNumber1 And a.Fnumber <= @FAccountNumber2 '
exec (@str)
GO
[解决办法]
CREATE PROCEDURE [dbo].[T_spImportK3Account]
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
AS
declare @sql varchar(8000)
set @sql = 'SELECT v.FVoucherID, convert(char(10),v.FDate,121) FDate,v.Fyear,v.Fperiod,v.FNumber,v.FEntryCount,
v.FDebitTotal, v.FChecked, v.FPreparerID,u1.FName as FPreparer,v.FCheckerID,u2.FName as FChecker,e.FAccountID,
a.FNumber FAccountNumber,a.FName as FAccountName, e.FDetailID,e.FDC, e.FAmount, e.FQuantity, e.FUnitPrice,
e.FExplanation,e.FEntryID
into #temp1
FROM '+@FBrnoName+ '.. t_Voucher v With (Readpast)
INNER JOIN '+@FBrnoName+ '..t_VoucherEntry e WITH (Readpast) ON v.FVoucherID=e.FVoucherID
INNER JOIN '+@FBrnoName '+..t_Account a ON e.FAccountID=a.FAccountID
Left outer join '+@FBrnoName+ '.. t_Account a1 ON e.FAccountID2=a1.FAccountID
Left outer join '+@FBrnoName+ '..t_User u1 on v.FPreparerID = u1.FUserID
Left outer join '+@FBrnoName+ '..t_User u2 on v.FCheckerID = u2.FUserID
WHERE convert(varchar(19),v.Fdate,120) > = '+convert(varchar(19),@Fdate1,120)+
' And convert(varchar(19),v.Fdate,120) <= '+convert(varchar(19),@Fdate2,120)+ 'AND
a.Fnumber > = '+@FAccountNumber1 '+ And a.Fnumber <= '+@FAccountNumber2
exec(@sql)
GO
[解决办法]
try:
CREATE PROCEDURE [dbo].[T_spImportK3Account]
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
AS
EXECUTE ( '
SELECT v.FVoucherID, convert(char(10),v.FDate,121) FDate,v.Fyear,v.Fperiod,v.FNumber,v.FEntryCount,v.FDebitTotal, v.FChecked,
v.FPreparerID,u1.FName as FPreparer,v.FCheckerID,u2.FName as FChecker,e.FAccountID, a.FNumber FAccountNumber,
a.FName as FAccountName, e.FDetailID,e.FDC, e.FAmount, e.FQuantity, e.FUnitPrice, e.FExplanation,e.FEntryID into #temp1
FROM '+@FBrnoName+ '..t_Voucher v With (Readpast) INNER JOIN '+@FBrnoName+ '..t_VoucherEntry e
WITH (Readpast) ON v.FVoucherID=e.FVoucherID INNER JOIN '+@FBrnoName+ '..t_Account a ON e.FAccountID=a.FAccountID
Left outer join '+@FBrnoName+ '..t_Account a1 ON e.FAccountID2=a1.FAccountID
Left outer join '+@FBrnoName+ '..t_User u1 on v.FPreparerID = u1.FUserID
Left outer join '+@FBrnoName+ '..t_User u2 on v.FCheckerID = u2.FUserID
WHERE v.Fdate > = ' ' '+@FdateFrom+ ' ' ' And v.Fdate <= ' ' '+@FdateEnd+ ' ' ' AND
a.Fnumber > = ' ' '+@FAccountNumberFrom+ ' ' ' And a.Fnumber <= ' ' '+@FAccountNumberEnd+ ' ' ' ')
GO
