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 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)
你这个用的一团糟 啊。怎么会有双引号啊。还有变量应该分出来。不应该直接字符串里面啊。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)
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) 我用了个测试代码 我自己测试你的代码也不行。 你看看你的
@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 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)
@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)
哥们我搞了个测试数据。报错 这个
消息 402,级别 16,状态 1,过程 export,第 9 行
数据类型 nvarchar 和 date 在 add 运算符中不兼容。
想不到 不知道啥原因。找半天没找到ADD运算符啊。
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)
我用了个测试代码 我自己测试你的代码也不行。 你看看你的