存储过程未声明变量

[qq_15142565]

有A,B表，用id和unti关联ID(ID),UNTI(生产线)，CUSTOMER (项目），DATE（日期），CODE（材料编码），USE(用量)
A表结构是 B表结构是
ID UNTI CUSTOMER DATE ID UNTI CODE USE
1 1# A 2014-10-20 1 1# A 100
2 1# A 2014-10-21 1 1# B 100
1 2# A 2014-10-21 1 2# A 100
2 1# B 100
我用行列转换后得到新表结构是
ID UNTI CUSTOMER DATE A B
1 1# A 2014-10-20 100 100
1 2# A 2014-10-21 100 0
2 1# A 2014-10-21 0 100
我创建了个存储过程，但是提示未声明变量，帮忙看下应该怎么修改

creater proc export
@unti    nvarchar(50),
@customer  nvarchar(50),
@date    date
as
declare @1 varchar(8000)
set @1='select unti,customer,date'
select @1=@1+',sum(case code when'''+code+"""then use else 0 end)'['+code+']' from (select distinct code from B)c
set @1=@1+' from (select * from a where @unti=unti and @customer=customer and @date=date)a left join b on a.id=b.id and a.unti=b.unti'
set @1=@1+' group by unti,customer,date'
exec(@1)

[xiaodongni]

应该不会吧，

[还在加载中灬]

create proc export
@unti    nvarchar(50),
@customer  nvarchar(50),
@date    date
as
declare @1 Nvarchar(4000)
set @1='select unti,customer,date'
select @1=@1+',sum(case code when'''+code+'''then use else 0 end)['+code+']' from (select distinct code from B)c
set @1=@1+' from (select * from a where @unti=unti and @customer=customer and @date=date)a left join b on a.id=b.id and a.unti=b.unti'
set @1=@1+' group by unti,customer,date'
exec SYS.sp_executesql @1,N'@unti nvarchar(50),@customer nvarchar(50),@date date',@unti,@customer,@date

你参考一下，我这边的用法需要的是NVARCHAR，不要改回去~

[reenjie]

create proc export
@unti    nvarchar(50),
@customer  nvarchar(50),
@date    date
as
declare @sql varchar(8000)
set @sql='select unti,customer,date'
select @sql=@sql+',sum(case code when '''+code+''' then use else 0 end)''['+code+']''' from (select distinct code from B)c
set @sql=@sql+' from (select * from a where unti='''+@unti+''' and customer='''+@customer+''' and date='+@date+')a left join b on a.id=b.id and a.unti=b.unti'
set @sql=@sql+' group by a.unti,a.customer,a.date'
exec(@sql)

[xiaodongni]

你这个用的一团糟 啊。怎么会有双引号啊。还有变量应该分出来。不应该直接字符串里面啊。

create proc export
@unti    nvarchar(50),
@customer  nvarchar(50),
@date    date
as
declare @1 varchar(8000)
set @1='select unti,customer,date'
select @1=@1+',sum(case code when '''+[code]+'''then [use] else 0 end) as'''
+quotename([code])+'' from (select distinct code from B)c
set @1=@1+' from (select * from a where '''+@unti+'''=unti and'''+ @customer+'''=customer and '''+@date+'''=date)a left join b on a.id=b.id and a.unti=b.unti'
set @1=@1+' group by unti,customer,date'
exec (@1)

[xiaodongni]

引用 3 楼 reenjie 的回复:

create proc export
@unti    nvarchar(50),
@customer  nvarchar(50),
@date    date
as
declare @sql varchar(8000)
set @sql='select unti,customer,date'
select @sql=@sql+',sum(case code when '''+code+''' then use else 0 end)''['+code+']''' from (select distinct code from B)c
set @sql=@sql+' from (select * from a where unti='''+@unti+''' and customer='''+@customer+''' and date='+@date+')a left join b on a.id=b.id and a.unti=b.unti'
set @sql=@sql+' group by a.unti,a.customer,a.date'
exec(@sql)

哥们我搞了个测试数据。报错 这个 消息 402，级别 16，状态 1，过程 export，第 9 行 数据类型 nvarchar 和 date 在 add 运算符中不兼容。 想不到 不知道啥原因。找半天没找到ADD运算符啊。

[xiaodongni]

引用 2 楼 ky_min 的回复:

create proc export
@unti    nvarchar(50),
@customer  nvarchar(50),
@date    date
as
declare @1 Nvarchar(4000)
set @1='select unti,customer,date'
select @1=@1+',sum(case code when'''+code+'''then use else 0 end)['+code+']' from (select distinct code from B)c
set @1=@1+' from (select * from a where @unti=unti and @customer=customer and @date=date)a left join b on a.id=b.id and a.unti=b.unti'
set @1=@1+' group by unti,customer,date'
exec SYS.sp_executesql @1,N'@unti nvarchar(50),@customer nvarchar(50),@date date',@unti,@customer,@date

你参考一下，我这边的用法需要的是NVARCHAR，不要改回去~

create  table a (id int, unti varchar(10),
customere varchar(10),date date )
insert into  a
values(1,'1#','A','2014-10-20'),
(2,'1#','A','2014-10-21'),
(1,'2#','A','2014-10-21')

create table b (id int,unti varchar(10),
code varchar(10),uses int)
insert into  b values
(1 ,'1#','A',100),
(1 ,'1#','B',100),
(1 ,'2#','A',100),
(2 ,'1#','B',100)

我用了个测试代码 我自己测试你的代码也不行。 你看看你的