Isnull 怎么不起作用呢？应该怎么改 高手看看（在线马上结贴）

Question

declare @sql varchar(4000)set @sql=''  
select @sql=@sql+',['+isnull(colnote,colname)+']='+colname from datadict  --这里的ISNULL没起作用
where tablename ='T_CallAfterService_Info'  and colListShow='yes' 
order by  consort asc 
set @sql='select '+stuff(@sql,1,1,'')+' from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC ' 
exec(@sql) 
错误信息：
消息 1038，级别 15，状态 4，第 1 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。 
消息 1038，级别 15，状态 4，第 1 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。 
select []=MustID,[呼叫流水编号 ]=CallID,[外线号码]=OutLineNum,[通话类型(0呼入、1回铃、2外呼、3漏接、4失败)]=TelType,[交流内容]=TalkContent,[地区]=Area,[媒体]=Media,[]=Product,[客户编号]=GID,[工号]=UID,[组]=UGroup,[开始时间]=StartTime,[结束时间]=EndTime,[通话时长]=CallTimeLong,[分机号]=Ext,[部门]=OU,[录音文件路径]=AudioPath,[操作人]=Operate from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC 

Answer 1

如果colnote的值为空字符串：['']，isnull是不会当作null处理的。

Answer 2

如果你colnote,colname两个都为空怎么办?

Answer 3

--try
select @sql=@sql+',['+isnull(colnote,colname)+']='+colname from datadict where len(ltrim(colnote + colname))>0

Answer 4

那有没有办法处理这个地方 ？让select []=MustID 中括号里面无内容时 处理一下。。？

Answer 5

你如果想转换''可以使用CASE语句,ISNULL,只针对NULL值

Answer 6

在这里不可能出现两者都为空的情况，不然 @sql 就为 null 了，:)

Answer 7

declare @sql varchar(4000)set @sql=''  
select @sql=@sql+',['+isnull(isnull(colnote,colname),'')+']='+isnull(colname,'') from datadict  --这里的ISNULL没起作用
where tablename ='T_CallAfterService_Info'  and colListShow='yes' 
order by  consort asc 
set @sql='select '+stuff(@sql,1,1,'')+' from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC ' 
exec(@sql)

Answer 8

上面语句怎么加入CASE 请赐教

Answer 9

--这样,去掉空值
select @sql=@sql+',['+isnull(colnote,colname)+']='+colname from datadict where len(ltrim(isnull(colnote,'') + isnull(colname,'')))>0

Answer 10

错误信息：
消息 1038，级别 15，状态 4，第 1 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。 
消息 1038，级别 15，状态 4，第 1 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。 
select []=MustID,[呼叫流水编号 ]=CallID,[外线号码]=OutLineNum,[通话类型(0呼入、1回铃、2外呼、3漏接、4失败)]=TelType,[交流内容]=TalkContent,[地区]=Area,[媒体]=Media,[]=Product,[客户编号]=GID,[工号]=UID,[组]=UGroup,[开始时间]=StartTime,[结束时间]=EndTime,[通话时长]=CallTimeLong,[分机号]=Ext,[部门]=OU,[录音文件路径]=AudioPath,[操作人]=Operate from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC 

Answer 11

消息 1038，级别 15，状态 4，第 1 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。 
消息 1038，级别 15，状态 4，第 1 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。 
select []=MustID,[呼叫流水编号 ]=CallID,[外线号码]=OutLineNum,[通话类型(0呼入、1回铃、2外呼、3漏接、4失败)]=TelType,[交流内容]=TalkContent,[地区]=Area,[媒体]=Media,[]=Product,[客户编号]=GID,[工号]=UID,[组]=UGroup,[开始时间]=StartTime,[结束时间]=EndTime,[通话时长]=CallTimeLong,[分机号]=Ext,[部门]=OU,[录音文件路径]=AudioPath,[操作人]=Operate from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC 

Answer 12

把你这个表datadict 的数据贴出来看看,
奇了怪了.

Answer 13

tablename ='T_CallAfterService_Info'  and colListShow='yes'
关键是这部分贴出来,不用全部贴出来

Answer 14

if exists (select * from sysobjects where id = OBJECT_ID('[DataDict]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [DataDict]CREATE TABLE [DataDict] (
[TableName] [varchar]  (50) NULL,
[SerialID] [int]  IDENTITY (1, 1)  NOT NULL,
[ColName] [varchar]  (50) NULL,
[ColLen] [int]  NULL,
[ColNote] [varchar]  (150) NULL DEFAULT ('无标题'),
[ColListShow] [nvarchar]  (100) NULL DEFAULT (N'yes'),
[ConShow] [nvarchar]  (100) NULL DEFAULT (N'yes'),
[ConType] [nvarchar]  (100) NULL DEFAULT ((1)),
[ConDSType] [nvarchar]  (100) NULL DEFAULT ((-1)),
[ColType] [varchar]  (50) NULL,
[ColPrk] [varchar]  (50) NULL DEFAULT ('no'),
[ColNull] [varchar]  (50) NULL DEFAULT ('no'),
[ColSearch] [varchar]  (50) NULL DEFAULT ('no'),
[ColDec] [nvarchar]  (100) NULL DEFAULT ((0)),
[ConSort] [int]  NULL)ALTER TABLE [DataDict] WITH NOCHECK ADD  CONSTRAINT [PK_DataDict] PRIMARY KEY  NONCLUSTERED ( [SerialID] )
SET IDENTITY_INSERT [DataDict] ON
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1097,'MustID',10,'yes','yes','1','-1','int','yes','no','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1098,'CallID',100,'呼叫流水编号','yes','yes','1','-1','nvarchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1099,'OutLineNum',50,'外线号码','yes','yes','1','-1','nvarchar','no','no','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1100,'TelType',50,'通话类型(0呼入、1回铃、2外呼、3漏接、4失败)','yes','yes','1','-1','nvarchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1101,'TalkContent',100,'交流内容','yes','yes','1','-1','nvarchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1102,'Area',100,'地区','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1103,'Media',100,'媒体','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1104,'Product',50,'yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1105,'GID',100,'客户编号','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1106,'UID',50,'工号','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1107,'UGroup',50,'组','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1108,'StartTime',23,'开始时间','yes','yes','1','-1','datetime','no','yes','no','3')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1109,'EndTime',23,'结束时间','yes','yes','1','-1','datetime','no','yes','no','3')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1110,'CallTimeLong',10,'通话时长','yes','yes','1','-1','int','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1111,'Ext',50,'分机号','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1112,'OU',50,'部门','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1113,'AudioPath',200,'录音文件路径','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT [DataDict] ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1114,'Operate',50,'操作人','yes','yes','1','-1','varchar','no','yes','no','0')SET IDENTITY_INSERT [DataDict] OFF

Answer 15

下面是表结构
USE [RuiTuDB]
GO
/****** 对象:  Table [dbo].[DataDict]    脚本日期: 06/26/2008 12:10:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DataDict](
[TableName] [varchar](50) NULL,
[SerialID] [int] IDENTITY(1,1) NOT NULL,
[ColName] [varchar](50) NULL,
[ColLen] [int] NULL,
[ColNote] [varchar](150) NULL CONSTRAINT [DF_DataDict_ColNote]  DEFAULT ('无标题'),
[ColListShow] [nvarchar](50) NULL CONSTRAINT [DF_DataDict_ColListShow]  DEFAULT (N'yes'),
[ConShow] [nvarchar](50) NULL CONSTRAINT [DF_DataDict_ConShow]  DEFAULT (N'yes'),
[ConType] [nvarchar](50) NULL CONSTRAINT [DF_DataDict_ConType]  DEFAULT ((1)),
[ConDSType] [nvarchar](50) NULL CONSTRAINT [DF_DataDict_ConDSType]  DEFAULT ((-1)),
[ColType] [varchar](50) NULL,
[ColPrk] [varchar](50) NULL CONSTRAINT [DF_DataDict_ColPrk]  DEFAULT ('no'),
[ColNull] [varchar](50) NULL CONSTRAINT [DF_DataDict_ColNull]  DEFAULT ('no'),
[ColSearch] [varchar](50) NULL CONSTRAINT [DF_DataDict_ColSearch]  DEFAULT ('no'),
[ColDec] [nvarchar](50) NULL CONSTRAINT [DF_DataDict_ColDec]  DEFAULT ((0)),
[ConSort] [int] NULL,
 CONSTRAINT [PK_DataDict] PRIMARY KEY CLUSTERED 
(
[SerialID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'TableName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'SerialID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'长度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColLen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColNote'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段列表显示1显示0不显示' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColListShow'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段编辑显示0不显示1作为编辑显示2作为只读显示3隐藏显示' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ConShow'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生成控件的类型(1文本框、2下拉列表框3文本编辑器4联动下拉列表5隐藏控件6不可以编辑的文本框7不可以编辑的下拉列表等)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ConType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'控件对应的数据源类型与字典分类表T_Cod_Type的ID想对应' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ConDSType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColPrk'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为空' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColNull'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否生成查询' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColSearch'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'小数位数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ColDec'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'控件页面输出顺序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DataDict', @level2type=N'COLUMN',@level2name=N'ConSort'

Answer 16

数据文本格式：
[code=SQL]
TableName                                          SerialID    ColName                                            ColLen      ColNote                                                                                                                                                ColListShow                                        ConShow                                            ConType                                            ConDSType                                          ColType                                            ColPrk                                             ColNull                                            ColSearch                                          ColDec                                             ConSort
-------------------------------------------------- ----------- -------------------------------------------------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
T_CallAfterService_Info                            1097        MustID                                             10                                                                                                                                                                 yes                                                yes                                                1                                                  -1                                                 int                                                yes                                                no                                                 no                                                 0                                                  NULL
T_CallAfterService_Info                            1098        CallID                                             100         呼叫流水编号                                                                                                                                                 yes                                                yes                                                1                                                  -1                                                 nvarchar                                           no                                                 yes                                                no                                                 0                                                  NULL
T_CallAfterService_Info                            1099        OutLineNum                                         50          外线号码                                                                                                                                                   yes                                                yes                                                1                                                  -1                                                 nvarchar                                           no                                                 no                                                 no                                                 0                                                  NULL
T_CallAfterService_Info                            1100        TelType                                            50          通话类型(0呼入、1回铃、2外呼、3漏接、4失败)                                                                                                                              yes                                                yes                                                1                                                  -1                                                 nvarchar                                           no                                                 yes                                                no                                                 0                                                  NULL
T_CallAfterService_Info                            1101        TalkContent                                        100         交流内容                                                                                                                                                   yes                                                yes                                                1                                                  -1                                                 nvarchar                                           no                                                 yes                                                no                                                 0                                                  NULL
T_CallAfterService_Info                            1102        Area                                               100         地区                                                                                                                                                     yes                                                yes                                                1                                                  -1                                                 varchar                                            no                                                 yes                                                no                                                 0                                                  NULL
[code]

Answer 17

貌似没有异常啊,都有数值的,
declare  @DataDict table(
[TableName] [varchar]  (50) NULL,
[SerialID] [int] NOT NULL,
[ColName] [varchar]  (50) NULL,
[ColLen] [int]  NULL,
[ColNote] [varchar]  (150) NULL,
[ColListShow] [nvarchar]  (100) NULL DEFAULT (N'yes'),
[ConShow] [nvarchar]  (100) NULL DEFAULT (N'yes'),
[ConType] [nvarchar]  (100) NULL DEFAULT ((1)),
[ConDSType] [nvarchar]  (100) NULL DEFAULT ((-1)),
[ColType] [varchar]  (50) NULL,
[ColPrk] [varchar]  (50) NULL DEFAULT ('no'),
[ColNull] [varchar]  (50) NULL DEFAULT ('no'),
[ColSearch] [varchar]  (50) NULL DEFAULT ('no'),
[ColDec] [nvarchar]  (100) NULL DEFAULT ((0)),
[ConSort] [int]  NULL)INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1097,'MustID',10,'yes','yes','1','-1','int','yes','no','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1098,'CallID',100,'呼叫流水编号','yes','yes','1','-1','nvarchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1099,'OutLineNum',50,'外线号码','yes','yes','1','-1','nvarchar','no','no','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1100,'TelType',50,'通话类型(0呼入、1回铃、2外呼、3漏接、4失败)','yes','yes','1','-1','nvarchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1101,'TalkContent',100,'交流内容','yes','yes','1','-1','nvarchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1102,'Area',100,'地区','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1103,'Media',100,'媒体','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1104,'Product',50,'yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1105,'GID',100,'客户编号','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1106,'UID',50,'工号','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1107,'UGroup',50,'组','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1108,'StartTime',23,'开始时间','yes','yes','1','-1','datetime','no','yes','no','3')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1109,'EndTime',23,'结束时间','yes','yes','1','-1','datetime','no','yes','no','3')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1110,'CallTimeLong',10,'通话时长','yes','yes','1','-1','int','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1111,'Ext',50,'分机号','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1112,'OU',50,'部门','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1113,'AudioPath',200,'录音文件路径','yes','yes','1','-1','varchar','no','yes','no','0')
INSERT @DataDict ([TableName],[SerialID],[ColName],[ColLen],[ColNote],[ColListShow],[ConShow],[ConType],[ConDSType],[ColType],[ColPrk],[ColNull],[ColSearch],[ColDec]) VALUES ( 'T_CallAfterService_Info',1114,'Operate',50,'操作人','yes','yes','1','-1','varchar','no','yes','no','0')select isnull(colnote,colname) as t from @datadict where len(ltrim(isnull(colnote,'') + isnull(colname,'')))>0 and colListShow='yes'
/*
t
----------
MustID
呼叫流水编号
外线号码
通话类型(0呼入、1回铃、2外呼、3漏接、4失败)
交流内容
地区
媒体
Product
客户编号
工号
组
开始时间
结束时间
通话时长
分机号
部门
录音文件路径
操作人
*/

Answer 18

declare @sql varchar(4000)set @sql=''  
select @sql=@sql+',['+isnull(colnote,colname)+']='+colname from @datadict  --这里的ISNULL没起作用
where tablename ='T_CallAfterService_Info'  and colListShow='yes' 
order by  consort asc 
set @sql='select '+stuff(@sql,1,1,'')+' from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC ' 
print @sql
/*
select [MustID]=MustID,[呼叫流水编号]=CallID,[外线号码]=OutLineNum,[通话类型(0呼入、1回铃、2外呼、3漏接、4失败)]=TelType,[交流内容]=TalkContent,[地区]=Area,[媒体]=Media,[Product]=Product,[客户编号]=GID,[工号]=UID,[组]=UGroup,[开始时间]=StartTime,[结束时间]=EndTime,[通话时长]=CallTimeLong,[分机号]=Ext,[部门]=OU,[录音文件路径]=AudioPath,[操作人]=Operate from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC 
*/
这个也是对的～

Answer 19

用它检测下你的表里是否有这样的数据：
select * from [DataDict] where isnull(colnote,'')=''

Answer 20

你的错误信息"select []"已经说明了你的colnote有=''的值存在。

Answer 21

用COALESCE处理吧:where COALESCE(colnote,'')=''或用:where colnote is null

Answer 22

declare @sql varchar(4000)set @sql=''  
select @sql=@sql+',['+isnull((case when colnote = '' then null else colnote end),colname)+']='+colname from datadict  --这里的ISNULL没起作用
where tablename ='T_CallAfterService_Info'  and colListShow='yes' 
order by  consort asc 
set @sql='select '+stuff(@sql,1,1,'')+' from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC ' 
exec(@sql)

Answer 23

怎么会这样啊 我这还是不行啊。。
declare @sql varchar(4000)set @sql=''  
select @sql=@sql+',['+isnull(colnote,colname)+']='+colname from datadict  --这里的ISNULL没起作用
where tablename ='T_CallAfterService_Info'  and colListShow='yes' 
order by  consort asc 
set @sql='select '+stuff(@sql,1,1,'')+' from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC ' print @sql
/*
select []=MustID,[呼叫流水编号 ]=CallID,[外线号码]=OutLineNum,[话务类型]=TelType,[交流内容]=TalkContent,[地区]=Area,[媒体]=Media,[产品]=Product,[客户编号]=GID,[工号]=UID,[组]=UGroup,[开始时间]=StartTime,[结束时间]=EndTime,[通话时长]=CallTimeLong,[分机号]=Ext,[部门]=OU,[录音文件路径]=AudioPath,[操作人]=Operate from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC 
*/

Answer 24

确实是有啊 就是colnote字段为空的时候 上面这个语句就能查出来 我现在就是要处理这种数据啊

Answer 25

这样试：
declare @sql varchar(4000)set @sql=''  
select @sql=@sql+',['+case when isnull(colnote,colname)='' then '错了，这为空了' else isnull(colnote,colname)  end +']='+colname from datadict  --这里的ISNULL没起作用
where tablename ='T_CallAfterService_Info'  and colListShow='yes' 
order by  consort asc 
set @sql='select '+stuff(@sql,1,1,'')+' from [T_CallAfterService_Info]  
where 1 = 1   Order by mustid DESC ' 
exec(@sql) 

Answer 26

这样处理就好啦！问题是我没有搞清楚 null 跟 '' 我把他们当一回事处理

Answer 27

你的print出来的语句是：
select []=MustID,已经很明显说明你colnote数据为空了。这个字段是你的select的别名，所以要么你补数据，要么随便起一个。

Answer 28

结贴：
最先找到错误原因的是
“完美行动 ”22楼
而最先给出答案的是
“happyflystone 
无枪狙击手 
等级:” 26楼