create proc GetAct_Log
(
@startTime datetime,
@endTime datetime,
@acctNo nvarchar(50),
@code_id decimal,
@bol bit --是否是代理商 0:非代理商 1:代理商
)
as
declare @str nvarchar(4000)
declare @startTab varchar(50)
declare @endTab varchar(50)
declare @account_id nvarchar(50)
select @account_id = account_id from tab_account where acctNo = @acctNo
set @startTab = 'act_'+cast(datepart(yy,@startTime) as varchar(50))+right('00'+cast(datepart(MM,@startTime) as varchar(50)),2)
set @endTab = 'act_'+cast(datepart(yy,@endTime) as varchar(50))+right('00'+cast(datepart(MM,@endTime) as varchar(50)),2)
drop table temp
select [Name] into temp from dbo.sysobjects where type='U' and [Name] like 'act_%' and [Name]>=@startTab and [Name] <=@endTab order by [Name]
declare mycursor cursor for
select * from temp
open mycursor
declare @Name varchar(50)
fetch next from MyCURSOR into @name
while(@@fetch_status=0)
begin
set @str = @str+ 'select account_id,code_id,charge_id,content,paymoney,mac,startTime,endTime from ' +@Name+' where '
if(@bol =1)
begin
set @str = @str + ' account_id like '+char(39)+ @account_id+'%'+char(39)
end
if(@bol=0)
begin
set @str = @str +' account_id = '+char(39)+@account_id+char(39)
end
set @str = @str + ' and startTime >= convert(datetime,'+char(39)+convert(varchar(20),@startTime)+char(39)+') and endTime <= convert(datetime,'+char(39)+convert(varchar(20),@endTime)+char(39)+')'
if(@code_id <> -1)
begin
set @str = @str + ' and code_id =' +cast(@code_id as varchar(20))
end
set @str = @str + ' union '
print @Name
fetch next from MyCURSOR into @Name
end
close MyCURSOR
deallocate MyCURSOR
print @str
set @str = left(@str,len(@str)-5)
set @str = 'select e.*,f.charge_name from (select c.*,d.code_name from (select a.*,b.account_name,b.acctNo from ('+@str+') a left join tab_account b on a.account_id = b.account_id ) c left join act_code d on c.code_id = d.code_id ) e left join charge_kind f on e.charge_id = f.charge_id'
exec sp_executesql @str
(
@startTime datetime,
@endTime datetime,
@acctNo nvarchar(50),
@code_id decimal,
@bol bit --是否是代理商 0:非代理商 1:代理商
)
as
declare @str nvarchar(4000)
declare @startTab varchar(50)
declare @endTab varchar(50)
declare @account_id nvarchar(50)
select @account_id = account_id from tab_account where acctNo = @acctNo
set @startTab = 'act_'+cast(datepart(yy,@startTime) as varchar(50))+right('00'+cast(datepart(MM,@startTime) as varchar(50)),2)
set @endTab = 'act_'+cast(datepart(yy,@endTime) as varchar(50))+right('00'+cast(datepart(MM,@endTime) as varchar(50)),2)
drop table temp
select [Name] into temp from dbo.sysobjects where type='U' and [Name] like 'act_%' and [Name]>=@startTab and [Name] <=@endTab order by [Name]
declare mycursor cursor for
select * from temp
open mycursor
declare @Name varchar(50)
fetch next from MyCURSOR into @name
while(@@fetch_status=0)
begin
set @str = @str+ 'select account_id,code_id,charge_id,content,paymoney,mac,startTime,endTime from ' +@Name+' where '
if(@bol =1)
begin
set @str = @str + ' account_id like '+char(39)+ @account_id+'%'+char(39)
end
if(@bol=0)
begin
set @str = @str +' account_id = '+char(39)+@account_id+char(39)
end
set @str = @str + ' and startTime >= convert(datetime,'+char(39)+convert(varchar(20),@startTime)+char(39)+') and endTime <= convert(datetime,'+char(39)+convert(varchar(20),@endTime)+char(39)+')'
if(@code_id <> -1)
begin
set @str = @str + ' and code_id =' +cast(@code_id as varchar(20))
end
set @str = @str + ' union '
print @Name
fetch next from MyCURSOR into @Name
end
close MyCURSOR
deallocate MyCURSOR
print @str
set @str = left(@str,len(@str)-5)
set @str = 'select e.*,f.charge_name from (select c.*,d.code_name from (select a.*,b.account_name,b.acctNo from ('+@str+') a left join tab_account b on a.account_id = b.account_id ) c left join act_code d on c.code_id = d.code_id ) e left join charge_kind f on e.charge_id = f.charge_id'
exec sp_executesql @str
解决方案 »
- sql2005 用B数据库的字段替换A数据库的字段
- 无法为此请求检索数据。 (Microsoft.SqlServer.SmoEnum),sql 2005维护计划无法保存。
- quatename函数的疑惑
- 如何取得前一天的数据~~~
- SQL Server中如何查询其它计算机上的Access数据库
- join不出我的結果來,我錯在哪了?
- OpenDataSource和openrowset只有SA的权限才能使用?
- *******求助:关于数据同步更新问题!!!***********
- 高分寻求做智能卡要用到C/C++、数据库中的哪些技术或其他相关技术均可,回答均有分
- 有数据库开发经验的大侠,帮帮忙!谢谢!!
- sql语句如何实现?
- 求SQL更新记录行语句?
set @str = left(@str,len(@str)-5)
set @str = 'select e.*,f.charge_name from (select c.*,d.code_name from (select a.*,b.account_name,b.acctNo from ('+@str+') a left join tab_account b on a.account_id = b.account_id ) c left join act_code d on c.code_id = d.code_id ) e left join charge_kind f on e.charge_id = f.charge_id'
exec sp_executesql @str
这段代码,再次给 @str 赋值,游标里面的递归相加的结果被新值替换掉了,所以没有得到你期望的结果
exec sp_executesql @str
这个地方的@str不就是游标里的值么```我做了处理后赋给新的@str了啊``重点是在
close MyCURSOR
deallocate MyCURSOR
print @str
这里都打印不出想要的@str
怎么回事``
declare @str nvarchar(4000)
set @str='' --应该加上这句``
--如果变量没有赋予初始值。和任何字符串拼接都是null,比如:
declare @str nvarchar(4000)
set @str=@str+'asfsafd'
set @str=@str+' bbbbbbb'
select @str--结果:
null