declare @maxLength intselect @maxLength =(case when sc.xtype<>'231'then sc.lengthelse sc.length/2 end )from syscolumns as sc where sc.id=object_id('MortgageMachinery') and sc.name='Manufacturer'print @maxLength
调试欢乐多
--〉
sc.xtype<>231不过应该没什么影响。
结果还是不正确,,
/****** 对象: Table [dbo].[MortgageMachinery] 脚本日期: 09/29/2011 10:50:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MortgageMachinery](
[AutoID] [int] IDENTITY(1,1) NOT NULL,
[MortgageMachineryID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Mortgager] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[MortgagerNumber] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Address] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[MortgageName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Type] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Amount] [int] NULL,
[Unit] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[InvoiceMoney] [decimal](18, 0) NULL,
[MortgageMoney] [decimal](18, 0) NULL,
[MortgageLimitTime] [int] NULL,
[EvaluateMoney] [decimal](18, 0) NULL,
[MortgageRate] [decimal](18, 0) NULL,
[InvoiceNo] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[InvoiceDate] [datetime] NULL,
[Manufacturer] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[DeviceBrand] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[SumPrice] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[Re] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[custID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[Valuation] [decimal](18, 0) NULL,
CONSTRAINT [pk_MortgageMachinery_autoID] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
select sc.xtype,
case when sc.xtype<>'231' then sc.length else sc.length/2 end
from syscolumns as sc
如果nvarchar(30),我就想取15,,其它型类的长度不做处理(eg:varchar(30)就取30, ),,
else ISNULL(sc.length,0) end )from syscolumns as sc where sc.id=object_id('MortgageMachinery') and sc.name='Manufacturer'print @maxLength
select convert(varchar(20),name)name,xtype,length from syscolumns where id=OBJECT_ID('MortgageMachinery')
/*
name xtype length
-------------------- ----- ------
AutoID 56 4
MortgageMachineryID 167 50
Mortgager 167 50
MortgagerNumber 167 50
Address 167 50
MortgageName 167 50
Type 167 50
Amount 56 4
Unit 167 50
InvoiceMoney 106 9
MortgageMoney 106 9
MortgageLimitTime 56 4
EvaluateMoney 106 9
MortgageRate 106 9
InvoiceNo 167 50
InvoiceDate 61 8
Manufacturer 231 60
DeviceBrand 231 60
SumPrice 231 60
Re 231 600
custID 167 20
Valuation 106 9(22 行受影响)
*/
select (case when sc.xtype<>231 then sc.length else sc.length/2 end )as maxLength
from syscolumns as sc where sc.id=object_id('MortgageMachinery') and sc.name='Manufacturer'
/*
maxLength
-----------
30(1 行受影响)*/
go
declare @maxLength int
select
case when sc.xtype='231'then sc.length/2 else sc.length end
from syscolumns as sc where sc.id=object_id('MortgageMachinery')
print @maxLength