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
@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
解决方案 »
- 求sql语句?(急)
- 我想在一个存储过程里面做两个插入
- 如果1个表有100个字段,如何用简单方法select得到其中的99个
- Sql 注入的一个简单问题
- 如何設定默認值,資料在修改之後如何保存原有資料
- 请问如何执行sp_add_jobschedule,为什么报找不到存储过程
- 求一名SQL语句,把A表数据导入B表
- 自学SQL Server第一天:一台普通的个人电脑,装上Windows2000 server+SQL Server2000拨号上网,别人怎样从Internet上访问我的SQL Server数据
- 关于报表,请您介绍一下MS-ReportingService ?
- 帮帮我!我想跳楼!哎!
- 判断某一列是否存在
- 查一个20几万条数据量的数据库 速度应该是多少~~~~~~~~~~~~~~~~~
把所有的变量和sql语句加成一个字符串
exec(组织的sql语句)
@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
@FBrnoName varchar(100),----数据库名
@FdateFrom datetime, ----起始日期
@FdateEnd datetime, ----终止日期
@FAccountNumberFrom nvarchar(100), ----起始科目
@FAccountNumberEnd nvarchar(100) ----终止科目
ASdeclare @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 <= '+@FAccountNumber2EXEC sp_executesql @Sql
@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
@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 <= '+@FAccountNumber2exec(@sql)
GO
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