create proc Proc_ShengChan_Notice_Tiaoma_NO_TempName
@Temp_Table_Name varchar(30),
@Order_NO AS VARCHAR(30)
AS
DECLARE @sql varchar(2000)
if exists(select name from sysobjects where id=object_id(@Temp_Table_Name))
begin
EXEC('drop table ' +@Temp_Table_Name)
end
with C_Temp AS
(select * ,rn=row_number()over(order by getdate()) from (SELECT distinct A.ID,c.订单号,b.国别,a.品号,a.品名,a.颜色,b.颜色英文,a.型号,b.顺序,a.楦号,a.UK码,a.客人号,b.条码 as 条码号,b.条码类别,sum(a.数量) as 数量,b.每箱数量,ceiling(cast(sum(a.数量) as float)/b.每箱数量) as 箱数 from sa_input_bill_dtl a
left join sys_material_table_ini_view b on a.品号=b.款号 and a.品名=b.品名 and a.颜色=b.颜色 and a.楦号=b.楦号 and a.UK码=b.UK码 and a.客人号=b.客人号 and a.型号=b.型号
left join sa_input_bill c on c.id=a.mainid and c.国别=b.国别
where c.订单号=@Order_NO
group by A.ID,c.订单号,b.国别,a.品号,a.品名,a.颜色,b.颜色英文,a.型号,b.顺序,a.楦号,a.UK码,a.客人号,b.条码,b.条码类别,b.每箱数量) A_Temp)
set @sql='select D_Temp.ID,D_Temp.订单号,D_Temp.国别,D_Temp.品号,D_Temp.品名,D_Temp.颜色,D_Temp.颜色英文,D_Temp.顺序,D_Temp.型号,D_Temp.楦号,D_Temp.UK码,D_Temp.客人号,D_Temp.条码号,D_Temp.条码类别,D_Temp.数量,D_Temp.每箱数量,D_Temp.箱数,ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+1)' + '-' + 'ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+D_Temp.箱数) 顺序号 into ' +@Temp_Table_Name+ ' from C_Temp D_Temp order by D_Temp.ID'------给sql变量赋值的过程中为什么不可以呢?
print @sql
exec(@sql)
@Temp_Table_Name varchar(30),
@Order_NO AS VARCHAR(30)
AS
DECLARE @sql varchar(2000)
if exists(select name from sysobjects where id=object_id(@Temp_Table_Name))
begin
EXEC('drop table ' +@Temp_Table_Name)
end
with C_Temp AS
(select * ,rn=row_number()over(order by getdate()) from (SELECT distinct A.ID,c.订单号,b.国别,a.品号,a.品名,a.颜色,b.颜色英文,a.型号,b.顺序,a.楦号,a.UK码,a.客人号,b.条码 as 条码号,b.条码类别,sum(a.数量) as 数量,b.每箱数量,ceiling(cast(sum(a.数量) as float)/b.每箱数量) as 箱数 from sa_input_bill_dtl a
left join sys_material_table_ini_view b on a.品号=b.款号 and a.品名=b.品名 and a.颜色=b.颜色 and a.楦号=b.楦号 and a.UK码=b.UK码 and a.客人号=b.客人号 and a.型号=b.型号
left join sa_input_bill c on c.id=a.mainid and c.国别=b.国别
where c.订单号=@Order_NO
group by A.ID,c.订单号,b.国别,a.品号,a.品名,a.颜色,b.颜色英文,a.型号,b.顺序,a.楦号,a.UK码,a.客人号,b.条码,b.条码类别,b.每箱数量) A_Temp)
set @sql='select D_Temp.ID,D_Temp.订单号,D_Temp.国别,D_Temp.品号,D_Temp.品名,D_Temp.颜色,D_Temp.颜色英文,D_Temp.顺序,D_Temp.型号,D_Temp.楦号,D_Temp.UK码,D_Temp.客人号,D_Temp.条码号,D_Temp.条码类别,D_Temp.数量,D_Temp.每箱数量,D_Temp.箱数,ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+1)' + '-' + 'ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+D_Temp.箱数) 顺序号 into ' +@Temp_Table_Name+ ' from C_Temp D_Temp order by D_Temp.ID'------给sql变量赋值的过程中为什么不可以呢?
print @sql
exec(@sql)
print @sql
exec(@sql)你PRINT 出来看一下
declare @Temp_Table_Name varchar(10),@sql nvarchar(2000)
set @Temp_Table_Name='#t'set @sql='select D_Temp.ID,D_Temp.订单号,D_Temp.国别,D_Temp.品号,D_Temp.品名,D_Temp.颜色
,D_Temp.颜色英文,D_Temp.顺序,D_Temp.型号,D_Temp.楦号,D_Temp.UK码,D_Temp.客人号,D_Temp.条码号,
D_Temp.条码类别,D_Temp.数量,D_Temp.每箱数量,D_Temp.箱数,ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+1)' + '+''-''+' + 'ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+D_Temp.箱数) 顺序号
into ' +@Temp_Table_Name+ '
from C_Temp D_Temp order by D_Temp.ID'------给sql变量赋值的过程中为什么不可以呢?
print @sql
select D_Temp.ID,D_Temp.订单号,D_Temp.国别,D_Temp.品号,D_Temp.品名,D_Temp.颜色
,D_Temp.颜色英文,D_Temp.顺序,D_Temp.型号,D_Temp.楦号,D_Temp.UK码,D_Temp.客人号,D_Temp.条码号,
D_Temp.条码类别,D_Temp.数量,D_Temp.每箱数量,D_Temp.箱数,ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+1)+'-'+ltrim(isnull((select sum(箱数) from C_Temp where rn<D_Temp.rn),0)+D_Temp.箱数) 顺序号
into #t
from C_Temp D_Temp order by D_Temp.ID
@Temp_Table_Name VARCHAR(30),
@Order_NO AS VARCHAR(30)
AS
DECLARE @sql VARCHAR(2000)
IF EXISTS(
SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID(@Temp_Table_Name)
)
BEGIN
EXEC ('DROP TABLE '+@Temp_Table_Name)
END
SELECT @sql =
'WITH C_Temp AS
(
SELECT *, rn = ROW_NUMBER()OVER(ORDER BY GETDATE())
FROM (
SELECT DISTINCT A.ID, c.订单号, b.国别, a.品号, a.品名, a.颜色, b.颜色英文, a.型号,
b.顺序, a.楦号, a.UK码, a.客人号, b.条码 AS 条码号, b.条码类别, SUM(a.数量) AS 数量,
b.每箱数量, CEILING(CAST(SUM(a.数量) AS FLOAT)/b.每箱数量) AS 箱数
FROM sa_input_bill_dtl a
LEFT JOIN sys_material_table_ini_view b
ON a.品号 = b.款号
AND a.品名 = b.品名
AND a.颜色 = b.颜色
AND a.楦号 = b.楦号
AND a.UK码 = b.UK码
AND a.客人号 = b.客人号
AND a.型号 = b.型号
LEFT JOIN sa_input_bill c
ON c.id = a.mainid
AND c.国别 = b.国别
WHERE c.订单号 = @Order_NO
GROUP BY A.ID, c.订单号, b.国别, a.品号, a.品名, a.颜色, b.颜色英文, a.型号, b.顺序,
a.楦号, a.UK码, a.客人号, b.条码, b.条码类别, b.每箱数量
) A_Temp
)
SELECT D_Temp.ID, D_Temp.订单号, D_Temp.国别, D_Temp.品号, D_Temp.品名, D_Temp.颜色, D_Temp.颜色英文,
D_Temp.顺序, D_Temp.型号, D_Temp.楦号, D_Temp.UK码, D_Temp.客人号, D_Temp.条码号, D_Temp.条码类别,
D_Temp.数量, D_Temp.每箱数量, D_Temp.箱数, LTRIM(
ISNULL(
(
SELECT SUM(箱数)
FROM C_Temp
WHERE rn<D_Temp.rn
), 0
)+1'+'-'+'LTRIM(
ISNULL(
(
SELECT SUM(箱数)
FROM C_Temp
WHERE rn<D_Temp.rn
), 0
)+D_Temp.箱数
) 顺序号
INTO '+@Temp_Table_Name+'
FROM C_Temp D_Temp
ORDER BY D_Temp.ID'------给sql变量赋值的过程中为什么不可以呢?
PRINT @sql
EXEC (@sql)try
改为 DECLARE @sql VARCHAR(8000)
@Temp_Table_Name VARCHAR(30),
@Order_NO AS VARCHAR(30)
AS
BEGIN
DECLARE @sql VARCHAR(2000)
IF EXISTS(
SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID(@Temp_Table_Name)
)
BEGIN
EXEC ('DROP TABLE '+@Temp_Table_Name)
END
SELECT @sql =
'WITH C_Temp AS
(
SELECT *, rn = ROW_NUMBER()OVER(ORDER BY GETDATE())
FROM (
SELECT DISTINCT A.ID, c.订单号, b.国别, a.品号, a.品名, a.颜色, b.颜色英文, a.型号,
b.顺序, a.楦号, a.UK码, a.客人号, b.条码 AS 条码号, b.条码类别, SUM(a.数量) AS 数量,
b.每箱数量, CEILING(CAST(SUM(a.数量) AS FLOAT)/b.每箱数量) AS 箱数
FROM sa_input_bill_dtl a
LEFT JOIN sys_material_table_ini_view b
ON a.品号 = b.款号
AND a.品名 = b.品名
AND a.颜色 = b.颜色
AND a.楦号 = b.楦号
AND a.UK码 = b.UK码
AND a.客人号 = b.客人号
AND a.型号 = b.型号
LEFT JOIN sa_input_bill c
ON c.id = a.mainid
AND c.国别 = b.国别
WHERE c.订单号 = @Order_NO
GROUP BY A.ID, c.订单号, b.国别, a.品号, a.品名, a.颜色, b.颜色英文, a.型号, b.顺序,
a.楦号, a.UK码, a.客人号, b.条码, b.条码类别, b.每箱数量
) A_Temp
)
SELECT D_Temp.ID, D_Temp.订单号, D_Temp.国别, D_Temp.品号, D_Temp.品名, D_Temp.颜色, D_Temp.颜色英文,
D_Temp.顺序, D_Temp.型号, D_Temp.楦号, D_Temp.UK码, D_Temp.客人号, D_Temp.条码号, D_Temp.条码类别,
D_Temp.数量, D_Temp.每箱数量, D_Temp.箱数, LTRIM(
ISNULL(
(
SELECT SUM(箱数)
FROM C_Temp
WHERE rn<D_Temp.rn
), 0
)+1'+'-'+'LTRIM(
ISNULL(
(
SELECT SUM(箱数)
FROM C_Temp
WHERE rn<D_Temp.rn
), 0
)+D_Temp.箱数
) 顺序号
INTO '+@Temp_Table_Name+'
FROM C_Temp D_Temp
ORDER BY D_Temp.ID'------给sql变量赋值的过程中为什么不可以呢?
PRINT @sql
EXEC (@sql)END运行看看。
@Temp_Table_Name VARCHAR(30),
@Order_NO AS VARCHAR(30)
AS
BEGIN
DECLARE @sql VARCHAR(max)
IF EXISTS(
SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID(@Temp_Table_Name)
)
BEGIN
EXEC ('DROP TABLE '+@Temp_Table_Name)
END
SELECT @sql =
'WITH C_Temp AS
(
SELECT *, rn = ROW_NUMBER()OVER(ORDER BY GETDATE())
FROM (
SELECT DISTINCT A.ID, c.订单号, b.国别, a.品号, a.品名, a.颜色, b.颜色英文, a.型号,
b.顺序, a.楦号, a.UK码, a.客人号, b.条码 AS 条码号, b.条码类别, SUM(a.数量) AS 数量,
b.每箱数量, CEILING(CAST(SUM(a.数量) AS FLOAT)/b.每箱数量) AS 箱数
FROM sa_input_bill_dtl a
LEFT JOIN sys_material_table_ini_view b
ON a.品号 = b.款号
AND a.品名 = b.品名
AND a.颜色 = b.颜色
AND a.楦号 = b.楦号
AND a.UK码 = b.UK码
AND a.客人号 = b.客人号
AND a.型号 = b.型号
LEFT JOIN sa_input_bill c
ON c.id = a.mainid
AND c.国别 = b.国别
WHERE c.订单号 = @Order_NO
GROUP BY A.ID, c.订单号, b.国别, a.品号, a.品名, a.颜色, b.颜色英文, a.型号, b.顺序,
a.楦号, a.UK码, a.客人号, b.条码, b.条码类别, b.每箱数量
) A_Temp
)
SELECT D_Temp.ID, D_Temp.订单号, D_Temp.国别, D_Temp.品号, D_Temp.品名, D_Temp.颜色, D_Temp.颜色英文,
D_Temp.顺序, D_Temp.型号, D_Temp.楦号, D_Temp.UK码, D_Temp.客人号, D_Temp.条码号, D_Temp.条码类别,
D_Temp.数量, D_Temp.每箱数量, D_Temp.箱数, LTRIM(
ISNULL(
(
SELECT SUM(箱数)
FROM C_Temp
WHERE rn<D_Temp.rn
), 0
)+1'+'-'+'LTRIM(
ISNULL(
(
SELECT SUM(箱数)
FROM C_Temp
WHERE rn<D_Temp.rn
), 0
)+D_Temp.箱数
) 顺序号
INTO '+@Temp_Table_Name+'
FROM C_Temp D_Temp
ORDER BY D_Temp.ID'------给sql变量赋值的过程中为什么不可以呢?
PRINT @sql
EXEC (@sql)END