declare @strWhere nvarchar(100)
set @strWhere=N' AND 1=1'
--报表2:模拟信息
select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion='1002606230192458' AND A3.Org='1000912250540038' + @strWhere
group by A4.ID我这样写会有错误:
消息 8114,级别 16,状态 5,第 12 行
从数据类型 nvarchar 转换为 bigint 时出错。
请问我的 @strWhere 要怎么样跟这个语句能连接在一起呢?
因为我是放在游标里面要执行的。我试过
exec sp_executesql ''这样的写法。游标里面会产生语法错误。
所以exec sp_executesql这个方法不使用现在。
那我要怎么样连接这个语句呢?谢谢。
declare @str varchar(max)
set @str = 'select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion=''1002606230192458'' AND A3.Org=''1000912250540038''' + @strWhere +'
group by A4.ID'
exec(@str)
declare @str nvarchar(4000)
declare @strWhere nvarchar(100)
set @strWhere=N' AND 1=1'
set @str = N'select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion=''1002606230192458'' AND A3.Org=''1000912250540038''' + @strWhere +'
group by A4.ID'
exec(@str)
declare @strWhere nvarchar(100)
set @strWhere=N' AND 1=1'
--报表2:模拟信息
select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion='1002606230192458' AND A3.Org=cast ('1000912250540038' as varchar(255) + @strWhere
group by A4.ID
declare @CBO_BOMID nvarchar(20)
declare @CBO_BOMNum decimal(24,9)
declare my_cursor_monitable cursor
for
--报表2:模拟信息
select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion='1002606230192458' and A.OwnerOrg=+@org AND A3.Org=+@org + @strWhere group by A4.ID
open my_cursor_monitable
fetch next from my_cursor_monitable into @CBO_BOMID,@CBO_BOMNum
while @@fetch_status=0
BEGIN
declare @getDateStr nvarchar(20)
set @getDateStr = convert(nvarchar(20),GETDATE(),120) --2010040913482609
declare @p11 nvarchar(max)
declare @sql_1 nvarchar(max)
set @sql_1='insert into ##Table_LKSHLM_GetBOMStructSumQuery select ID,ItemFormAttribute,ItemMaster,ItemCode,ItemName,RequireNum,UOMName from '+@p11 exec (@sql_1)
fetch next from my_cursor_monitable into @CBO_BOMID,@CBO_BOMNum
END
close my_cursor_monitable
DEALLOCATE my_cursor_monitable
我这个是放在游标里面去执行的。
游标里面不认这个
exec会出错误。请问不用动态SQL 要怎么弄?
请问不要用exec的方法了,还有没有其它方法?
group by A4.ID
2楼的方法不错啊
游标不认动态的 SQL语句。所以没法做了。
这个方法不行啊。你随便弄个数据执行一下。还是一样的错误。
消息 8114,级别 16,状态 5,第 8 行
从数据类型 nvarchar 转换为 bigint 时出错。
set @strWhere=N' AND 1=1'
--报表2:模拟信息
select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion='1002606230192458' AND A3.Org='1000912250540038' + @strWhere
group by A4.ID不拼接不行的,只能用exec
不然'1000912250540038' + @strWhere只是一个常量+变量值也就是A3.org='xxx'而不是
A3.Org='1000912250540038' and 1=1
declare @stmt
@Stmt = 'Declare test_cur Cursor Fast_Forward for'+
'select * from table'
Exec (@Stmt)
Open test_cur
Fetch Next from test_cur into ******
While @@FETCH_STATUS = 0 Begin
...
Fetch Next from cur_OL_Obj into @DBObjectKey
End
Close test_cur
Deallocate test_cur
放游标里的动态sql需要把声明游标也放到动态sql里
declare @stmt varchar(max)
@Stmt = 'Declare test_cur Cursor Fast_Forward for'+
'select * from table'
Exec (@Stmt)
Open test_cur
Fetch Next from test_cur into ******
While @@FETCH_STATUS = 0 Begin
...
Fetch Next from test_cur into ******
End
Close test_cur
Deallocate test_cur
下面的游标
消息 16916,级别 16,状态 1,过程 LKSHLM_ShengChanMoNiBaoBiao_ZLY,第 93 行
名为 'my_cursor_monitable' 的游标不存在。declare @StrSql nvarchar(max)
declare @CBO_BOMID nvarchar(20)
declare @CBO_BOMNum decimal(24,9)
declare @Str_cursor nvarchar(max)
set @Str_cursor = 'declare my_cursor_monitable cursor for '+
'select A4.ID,sum(A1.IUQty)
from MRP_MPS as A left join MRP_MPSDetail as A1 on A.ID=A1.MPS
left join CBO_ItemMaster as A3 on A.ItemCode=A3.Code
left join CBO_BOMMaster as A4 on A3.ID=A4.ItemMaster
where A.PlanVersion=''1002606230192458'' AND A3.Org='+@org+'' + @strWhere +'
group by A4.ID'
exec(@StrSql)
open my_cursor_monitable
fetch next from my_cursor_monitable into @CBO_BOMID,@CBO_BOMNum
while @@fetch_status=0
BEGIN
declare @getDateStr nvarchar(20)
set @getDateStr = convert(nvarchar(20),GETDATE(),120) --2010040913482609
declare @p11 nvarchar(max)
declare @sql_1 nvarchar(max)
exec BOM_P_BOMStructSumQuery @BOM=@CBO_BOMID,@AlternateType=0,@EffDate=@getDateStr,@UOM=0,@RequireNum=@CBO_BOMNum,@Expand=0,@FilterItemType=N'',@SysMLFlag=N'zh-CN',@Project=N'-10',@OwnerOrg=-1,@ResultTableName=@p11 output
set @sql_1='insert into ##Table_LKSHLM_GetBOMStructSumQuery select ID,ItemFormAttribute,ItemMaster,ItemCode,ItemName,RequireNum,UOMName from '+@p11 exec (@sql_1)
fetch next from my_cursor_monitable into @CBO_BOMID,@CBO_BOMNum
END
close my_cursor_monitable
DEALLOCATE my_cursor_monitable
/*游标结束*/
没执行游标。现在可以了。
终于成功了。谢谢。