执行存储过程总是报 临时表无效解决思路
执行存储过程总是报 临时表无效
USE [wfp]
GO
/****** Object: StoredProcedure [dbo].[pr_T接口_一代_上下架_网店产品_读取] Script Date: 03/28/2013 19:12:13 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr_T接口_一代_上下架_网店产品_读取]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr_T接口_一代_上下架_网店产品_读取]
GO
USE [wfp]
GO
/****** Object: StoredProcedure [dbo].[pr_T接口_一代_上下架_网店产品_读取] Script Date: 03/28/2013 19:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[pr_T接口_一代_上下架_网店产品_读取]
@dbname varchar(20) --数据库名称
,@wfpuser varchar(100) --操作账号
,@shopid varchar(100) --店铺编号
,@shoptype varchar(100) --店铺类型
as
/*
[调用]
exec wfp..[pr_t接口_一代_上下架_网店产品_读取]
@dbname = N'edb_asqldev.',
@wfpuser = N'edb_b000035',
@shopid = N'7',
@shoptype = N'淘宝'
select * from edb_asqldev..wfpuser_a0305
[功能]:读取网店产品数据,用于执行上下架操作
[输入]:店铺信息,详细请参风参数说明
[输出]:用于执行上下架的数据集
[实现逻辑]
1.判断库存计算方式m..
2.判断是否开启预售
3.计算产品库存
4.网店产品关联库存,并分析执行操作,上架还是下架
5.返回上下架结果集
[作者]:
[版本号]:v1.0
[最后修改时间]:2013-3-14
[历史修改记录]:
-------------------
修改人：朱熹-刘津防
修改原因：为了实现数据同步平台自动上下架
修改内容：1.将启用上下架（备用4）重命名为 是否启用上下架
2.是否更新条形码 默认为False
3.添加自动上下架状态读取 （默认更新，启用更新，不启用更新）
-------------------
*/
exec('if object_id('''+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload'') is not null drop table '+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload
if object_id('''+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload_d'') is not null drop table '+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload_d ')
declare @sqltz nvarchar(max)
set @sqltz='
if object_id(''logs..'+@wfpuser+'wfpuser_a0304库存临时表'') is not null drop table logs..'+@wfpuser+'wfpuser_a0304库存临时表
DECLARE @ac0052 nvarchar(500)
SELECT @ac0052=ac0052
FROM '+@dbname+'.wfpcode_ad0527 a
JOIN '+@dbname+'.wfpuser_a0401 b
ON Charindex('',''+Convert(VARCHAR,b.objorder)+'','','',''+Isnull(a.ac0052,'''')+'','')>0
WHERE a.objname='''+@shopid+''' and a.ad0554='''+case when @shoptype ='分销平台' then '淘宝分销' else @shoptype end+'''
AND Isnull(b.ab0007,0)=0
SELECT @ac0052=Isnull(@ac0052,1)
select a.Objid,(case when isnull(sum(b.TN0010),0)>0 then isnull(sum(b.TN0010),0) else 0 end ) tn0010all
,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.TN0010 else 0 end),0) tn0010
,isnull(SUM(b.ai0018),0) ai0018all,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.ai0018 else 0 end),0) ai0018
,isnull(SUM(b.ti0085),0) ti0085all,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.ti0085 else 0 end),0) ti0085
,isnull(SUM(b.tn0017),0) tn0017all,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.tn0017 else 0 end),0) ti0017
,isnull(max(convert(int,b.ab0041)),0) ab0041
into logs..'+@wfpuser+'wfpuser_a0304库存临时表
from '+@dbname+'.wfpuser_a0304 a
left join '+@dbname+'.wfpuser_a0307 b
on b.kc0001=a.objid
left JOIN '+@dbname+'.wfpuser_a0401 c
ON b.ac0016=c.objorder
WHERE a.objid>0
AND Isnull(c.ab0007,0)=0
GROUP BY a.objid
declare @AllKucunNum int
select @AllKucunNum= isnull((select isnull(objjc,0) from '+@dbname+'.WFPCODE_AD504 where objname=''同步全仓库存''),0)
'
print (@sqltz)
begin
--declare @sql varchar(8000),@upCount varchar(2000)
declare @sql3 nvarchar(max),@sql4 nvarchar(max)
set @sql3='
declare @AllKucunNum int
select @AllKucunNum= isnull((select isnull(objjc,0) from '+@dbname+'.WFPCODE_AD504 where objname=''同步全仓库存''),0)
select b.objid,条形码,商品id,商品数字id,商品属性,产品名称 as 网店产品名称,b.规格 as 规格,是否主产品,子产品存在,b.备用2 as 上次更新数量,
CASE
WHEN a3.objid is null THEN 0
WHEN @allkucunnum=0 THEN
case when a3.ab0041=0 then convert(int,a3.tn0010-a3.ai0018 -a3.ti0085) else a3.ti0017 end
ELSE
case when a3.ab0041=0 then convert(int,a3.tn0010all -a3.ai0018all -a3.ti0085all) else a3.tn0017all end
END AS 图片库存
,c.EC0104 as 品名,
null as 数量,cast (null as varchar(200)) as 网店产品状态,
cast (null as varchar(200)) as 操作结果,''False'' as 选择,
USE [wfp]
GO
/****** Object: StoredProcedure [dbo].[pr_T接口_一代_上下架_网店产品_读取] Script Date: 03/28/2013 19:12:13 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr_T接口_一代_上下架_网店产品_读取]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr_T接口_一代_上下架_网店产品_读取]
GO
USE [wfp]
GO
/****** Object: StoredProcedure [dbo].[pr_T接口_一代_上下架_网店产品_读取] Script Date: 03/28/2013 19:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[pr_T接口_一代_上下架_网店产品_读取]
@dbname varchar(20) --数据库名称
,@wfpuser varchar(100) --操作账号
,@shopid varchar(100) --店铺编号
,@shoptype varchar(100) --店铺类型
as
/*
[调用]
exec wfp..[pr_t接口_一代_上下架_网店产品_读取]
@dbname = N'edb_asqldev.',
@wfpuser = N'edb_b000035',
@shopid = N'7',
@shoptype = N'淘宝'
select * from edb_asqldev..wfpuser_a0305
[功能]:读取网店产品数据,用于执行上下架操作
[输入]:店铺信息,详细请参风参数说明
[输出]:用于执行上下架的数据集
[实现逻辑]
1.判断库存计算方式m..
2.判断是否开启预售
3.计算产品库存
4.网店产品关联库存,并分析执行操作,上架还是下架
5.返回上下架结果集
[作者]:
[版本号]:v1.0
[最后修改时间]:2013-3-14
[历史修改记录]:
-------------------
修改人：朱熹-刘津防
修改原因：为了实现数据同步平台自动上下架
修改内容：1.将启用上下架（备用4）重命名为 是否启用上下架
2.是否更新条形码 默认为False
3.添加自动上下架状态读取 （默认更新，启用更新，不启用更新）
-------------------
*/
exec('if object_id('''+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload'') is not null drop table '+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload
if object_id('''+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload_d'') is not null drop table '+@dbname+'.'+@wfpuser+'wfpuser_a0304autoUpload_d ')
declare @sqltz nvarchar(max)
set @sqltz='
if object_id(''logs..'+@wfpuser+'wfpuser_a0304库存临时表'') is not null drop table logs..'+@wfpuser+'wfpuser_a0304库存临时表
DECLARE @ac0052 nvarchar(500)
SELECT @ac0052=ac0052
FROM '+@dbname+'.wfpcode_ad0527 a
JOIN '+@dbname+'.wfpuser_a0401 b
ON Charindex('',''+Convert(VARCHAR,b.objorder)+'','','',''+Isnull(a.ac0052,'''')+'','')>0
WHERE a.objname='''+@shopid+''' and a.ad0554='''+case when @shoptype ='分销平台' then '淘宝分销' else @shoptype end+'''
AND Isnull(b.ab0007,0)=0
SELECT @ac0052=Isnull(@ac0052,1)
select a.Objid,(case when isnull(sum(b.TN0010),0)>0 then isnull(sum(b.TN0010),0) else 0 end ) tn0010all
,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.TN0010 else 0 end),0) tn0010
,isnull(SUM(b.ai0018),0) ai0018all,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.ai0018 else 0 end),0) ai0018
,isnull(SUM(b.ti0085),0) ti0085all,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.ti0085 else 0 end),0) ti0085
,isnull(SUM(b.tn0017),0) tn0017all,isnull(SUM(case when Charindex('',''+b.ac0016+'','','',''+@ac0052+'','')>0 then b.tn0017 else 0 end),0) ti0017
,isnull(max(convert(int,b.ab0041)),0) ab0041
into logs..'+@wfpuser+'wfpuser_a0304库存临时表
from '+@dbname+'.wfpuser_a0304 a
left join '+@dbname+'.wfpuser_a0307 b
on b.kc0001=a.objid
left JOIN '+@dbname+'.wfpuser_a0401 c
ON b.ac0016=c.objorder
WHERE a.objid>0
AND Isnull(c.ab0007,0)=0
GROUP BY a.objid
declare @AllKucunNum int
select @AllKucunNum= isnull((select isnull(objjc,0) from '+@dbname+'.WFPCODE_AD504 where objname=''同步全仓库存''),0)
'
print (@sqltz)
begin
--declare @sql varchar(8000),@upCount varchar(2000)
declare @sql3 nvarchar(max),@sql4 nvarchar(max)
set @sql3='
declare @AllKucunNum int
select @AllKucunNum= isnull((select isnull(objjc,0) from '+@dbname+'.WFPCODE_AD504 where objname=''同步全仓库存''),0)
select b.objid,条形码,商品id,商品数字id,商品属性,产品名称 as 网店产品名称,b.规格 as 规格,是否主产品,子产品存在,b.备用2 as 上次更新数量,
CASE
WHEN a3.objid is null THEN 0
WHEN @allkucunnum=0 THEN
case when a3.ab0041=0 then convert(int,a3.tn0010-a3.ai0018 -a3.ti0085) else a3.ti0017 end
ELSE
case when a3.ab0041=0 then convert(int,a3.tn0010all -a3.ai0018all -a3.ti0085all) else a3.tn0017all end
END AS 图片库存
,c.EC0104 as 品名,
null as 数量,cast (null as varchar(200)) as 网店产品状态,
cast (null as varchar(200)) as 操作结果,''False'' as 选择,