CREATE PROCEDURE xx_scjdxx as
declare @sqls varchar(8000)
select
@sqls=isnull(@sqls+',','')
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.daoc end) as [道次'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.gg end) as [规格'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.zs end) as [支数'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.jdrq end) as [日期'+ltrim(daoc)+']'
from
(select distinct daoc from v_scjdb) ttt
set @sqls='select v_sckhxx.kh as 框号,v_sckhxx.khmc as 客户名称,v_sckhxx.htbh as 合同号,v_sckhxx.gg as 成品规格,v_sckhxx.khrq,'
+@sqls
+' from v_sckhxx,v_scjdb where v_scjdb.kh=v_sckhxx.kh group by v_sckhxx.khrq,v_sckhxx.htbh,v_sckhxx.kh,v_sckhxx.khmc,v_sckhxx.gg'
exec (@sqls)
GO
这个存贮过程 为什么在WIN2003+SQL2000 环境下运行无误,而在WIN2008+SQL2000 下运行错误 提示如下:
服务器:消息 156,级别 15,状态 1,行1
在关键字 ‘THEN’附近有语法错误我在存储过程中 检查语法 无错误呀
是不是和 winows server2008 64位系统 有关系?
CREATE PROCEDURE xx_scjdxx as
declare @sqls varchar(8000)
select
@sqls=isnull(@sqls+',','')
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.daoc end) as [道次'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.gg end) as [规格'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.zs end) as [支数'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.jdrq end) as [日期'+ltrim(daoc)+']'
from
(select distinct daoc from v_scjdb) ttt
set @sqls='select v_sckhxx.kh as 框号,v_sckhxx.khmc as 客户名称,v_sckhxx.htbh as 合同号,v_sckhxx.gg as 成品规格,v_sckhxx.khrq,'
+@sqls
+' from v_sckhxx,v_scjdb where v_scjdb.kh=v_sckhxx.kh group by v_sckhxx.khrq,v_sckhxx.htbh,v_sckhxx.kh,v_sckhxx.khmc,v_sckhxx.gg'
exec (@sqls)
GO-- 是不是多了个逗号
上述 存储过程 无问题
并在 VB+SQL2000 WIN2003 测试通过。
试下这样行不行?CREATE PROCEDURE xx_scjdxx as
declare @sqls varchar(8000)
set @sqls=null
select
@sqls=isnull(@sqls+',','')
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.daoc end) as [道次'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.gg end) as [规格'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.zs end) as [支数'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.jdrq end) as [日期'+ltrim(daoc)+']'
from
(select distinct daoc from v_scjdb) ttt
set @sqls='select v_sckhxx.kh as 框号,v_sckhxx.khmc as 客户名称,v_sckhxx.htbh as 合同号,v_sckhxx.gg as 成品规格,v_sckhxx.khrq,'
+@sqls
+' from v_sckhxx,v_scjdb where v_scjdb.kh=v_sckhxx.kh group by v_sckhxx.khrq,v_sckhxx.htbh,v_sckhxx.kh,v_sckhxx.khmc,v_sckhxx.gg'
exec (@sqls)
GO
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.gg end) as [规格'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.zs end) as [支数'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.jdrq end) as [日期'+ltrim(daoc)+']'这4行的行尾有没有空格,删除后重新试一下。我曾经遇到的在别人机器上可以运行,发给我就不行了,仔细检测后发现行尾有空格,删除就好了。
你试试。
declare @sqls varchar(8000)
select
@sqls=isnull(@sqls+',','')+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.daoc end) as [道次'+ltrim(daoc)+'],'+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.gg end) as [规格'+ltrim(daoc)+'],'+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.zs end) as [支数'+ltrim(daoc)+'],'+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.jdrq end) as [日期'+ltrim(daoc)+']'
from
(select distinct daoc from v_scjdb) ttt
set @sqls='select v_sckhxx.kh as 框号,v_sckhxx.khmc as 客户名称,v_sckhxx.htbh as 合同号,v_sckhxx.gg as 成品规格,v_sckhxx.khrq,'
+@sqls
+' from v_sckhxx,v_scjdb where v_scjdb.kh=v_sckhxx.kh group by v_sckhxx.khrq,v_sckhxx.htbh,v_sckhxx.kh,v_sckhxx.khmc,v_sckhxx.gg'
exec (@sqls)
GO
你检测一下SQL code +'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.daoc end) as [道次'+ltrim(daoc)+'],'
+'max(case when v_scjdb.daoc='+ ltrim(daoc)+' then v_scjdb.gg end) as [规格'+ltrim(……
还是一样
就是需要 2008 的数据 和 2003 系统下的 存储过程 很麻烦呀
还我 还重装了 SQL 郁闷呀!!!!!