create proc prou_bankAccountDayBook(
@year varchar(10),@month varchar(10),@monthType varchar(2),@startDate varchar(12),@endDate varchar(12),@bankAccount varchar(20),@compId varchar(20))
as
declare @compBankAccount varchar(100)
declare @sqlStr varchar(5000)
set @compBankAccount=''
if @bankAccount<>''
set @compBankAccount=@compBankAccount + 'and bank.bankAccount like (''%'+ @bankAccount +'''%'')'
if @compId<>''
set @compBankAccount=@compBankAccount + 'and comp.compId like (''%'+ @compId +'''%'')'
begin
select * from (
select '00' as id,comp.compId,comp.compName,iYear as billYear,iMonth as billMonth,'期初' as billType,@startDate as billDate,bank.currId,currName,'' as billId
, sum(0.00) AS [In_acc], sum(0.00) AS [Exp_acc],sum(preBalance) as [Last_acc]
from balance,bank,comp,currency
where balance.bankAccount=bank.bankAccount and bank.compId=comp.compId and bank.currId=currency.currId and iyear=@year and imonth=@month and monthType=@monthType + @compBankAccount
group by iYear,iMonth,comp.compId,comp.compName,bank.currId,currName
union
SELECT billData.id,comp.compId,comp.compName,billYear,billMonth,billType,billDate,bank.currId,currName,billId
,case when billtype ='收' then moneylow else 0 end AS [In_acc], case when billtype ='付' then moneylow else 0 end AS [Exp_acc],0.00 as [Last_acc]
FROM billdata,bank,comp,currency
where billData.bankAccount=bank.bankAccount and bank.compId=comp.compId and bank.currId=currency.currId and billData.otherBankAccount not in (select bankAccount from bank)
and billType in ('收','付') AND (billDate>=@startDate and billDate<=@endDate ) and monthType=@monthType + @compBankAccount
) billData order by billyear,billmonth,compId,currId,currName,billDate,billData.id
exec @sqlStr
end
go
这个SQL语句是错误的
这个@compBankAccount 怎么样才能用起来,求正确的Sql语句!
解决方案 »
- 多条件SQL语句改写成一句(1个表数据)
- SQL语句求解
- sql语句计算某一列值的平均分之和
- xp home 版想装SQLSERVER, 应该装哪个版本?谢谢提供下载地址。
- 【100分难题】Foxpro6.0数据导入到Sqlserver2005报Feature is not available.
- 数据库设计问题,有胆你就来!
- 每个字段相加并列出符合条件的记录
- 马上结帖!----!!关于2个字符串比较函数,很简单的!!
- 求一软件酬谢!
- 从一个数据库A到另一个数据库B 复制数据的问题
- 怎样查询 数据库 某段 时间 某个表的 数据
- 请问如何实现若当天没有数据,则自动查询前一天的数据,直到有数据为止,谢谢
print出来看看啊
create proc prou_bankAccountDayBook(
@year varchar(10),@month varchar(10),@monthType varchar(2),@startDate varchar(12),@endDate varchar(12),@bankAccount varchar(20),@compId varchar(20))
as
declare @compBankAccount varchar(100)
declare @sqlStr varchar(5000)
set @compBankAccount=''
if @bankAccount<>''
set @compBankAccount=@compBankAccount + 'and bank.bankAccount like (''%'+ @bankAccount +'''%'')'
if @compId<>''
set @compBankAccount=@compBankAccount + 'and comp.compId like (''%'+ @compId +'''%'')'
BEGIN
SET @sqlStr='select * from (
select ''00'' as id,comp.compId,comp.compName,iYear as billYear,iMonth as billMonth,''期初'' as billType,'''+@startDate+''' as billDate,bank.currId,currName,'''' as billId
, sum(0.00) AS [In_acc], sum(0.00) AS [Exp_acc],sum(preBalance) as [Last_acc]
from balance,bank,comp,currency
where balance.bankAccount=bank.bankAccount and bank.compId=comp.compId and bank.currId=currency.currId and iyear='''+@year+''' and imonth=@month and monthType='''+@monthType+@compBankAccount+'''
group by iYear,iMonth,comp.compId,comp.compName,bank.currId,currName
union
SELECT billData.id,comp.compId,comp.compName,billYear,billMonth,billType,billDate,bank.currId,currName,billId
,case when billtype =''收'' then moneylow else 0 end AS [In_acc], case when billtype =''付'' then moneylow else 0 end AS [Exp_acc],0.00 as [Last_acc]
FROM billdata,bank,comp,currency
where billData.bankAccount=bank.bankAccount and bank.compId=comp.compId and bank.currId=currency.currId and billData.otherBankAccount not in (select bankAccount from bank)
and billType in (''收'',''付'') AND (billDate>='''+@startDate+''' and billDate<='''+@endDate+''' ) and monthType='''+@monthType+@compBankAccount+'''
) billData order by billyear,billmonth,compId,currId,currName,billDate,billData.id'PRINT @sqlStr --将动态sql打印出来检验exec (@sqlStr)