如果字符串超过8000字符，在SQL查询分析器用什么类型？

[devilwind]

在SQL查询分析器里我尝试过sql_variant，但只要超过一定字符，就会报和TEXT不兼容，但又是不能直接在SQL查询分析器里对变量进行定义的，请各位高手帮忙！

[lynx1111]

text,ntext

[lynx1111]

但又是不能直接在SQL查询分析器里对变量进行定义
什么意思？

declare @str text --定义一个text型变量

[devilwind]

"declare @str text --定义一个text型变量"
这样不行的，有以下错误：

服务器: 消息 2739，级别 16，状态 1，行 1
对于局部变量，text、ntext 和 image 数据类型无效。

[zjcxc]

text类型只能用于存储过程的参数.不能直接定义,也不能直接操作.

你可以考虑用临时表的方法.下面给过例子:
/*--text字段的替换处理
--*/
--创建数据测试环境
create table #tb(aa text)
insert into #tb
select 'abc123abc123,asd'

--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='123' --要替换的字符串
,@d_str='000' --替换成的字符串

--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
updatetext #tb.aa @p @postion @rplen @d_str
select @postion=charindex(@s_str,aa)-1 from #tb
end

--显示结果
select * from #tb

--删除数据测试环境
drop table #tb

[zjcxc]

如果是生成SQL时的问题,楼主可以参考我的贴子:

化解字符串不能超过8000的方法
http://expert.csdn.net/Expert/topic/2303/2303308.xml?temp=2.758425E-02

[zjcxc]

楼主最好给出你的处理实例.

[太空11]

text应该是可以的

[devilwind]

实例：

/*利用MSDescription建立视图*/

IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'ITP_CREATE_VIEW' AND Type = 'P') AND SUSER_SNAME() = 'sa'
DROP PROCEDURE ITP_CREATE_VIEW
GO
CREATE PROCEDURE ITP_CREATE_VIEW(
@TableName sysname = NULL, /*表名*/
@TFlag INTEGER = NULL, /*判断表名是不是有中文*/
@FFlag INTEGER = NULL /*判断字段名是不是有中文*/
)
WITH ENCRYPTION
AS
BEGIN
DECLARE @SQL_CODE1 NVARCHAR(4000),
@SQL_CODE2 NVARCHAR(4000),
@SQL_CODE3 NVARCHAR(4000),
@SQL_STR NVARCHAR(4000),
@SQL_COLUMN_STR NVARCHAR(4000),
@SEL_FIELD NVARCHAR(4000),
@FIELD_STR NVARCHAR(300),
@FLOAT_FIELD_STR NVARCHAR(300),
@Type VARCHAR(100),
@ViewName sysname,
@CName sysname,
@VName sysname

SELECT @TFlag = ISNULL(@TFlag,1)
SELECT @FFlag = ISNULL(@FFlag,1)

IF @TFlag <> 1 SELECT @TFlag = 0
IF @FFlag <> 1 SELECT @FFlag = 0

SELECT @SQL_CODE1 = N'IF EXISTS(SELECT 1 FROM sysobjects WHERE name = ''View_$1'' AND Type = ''V'') DROP VIEW [View_$1] ',
@SQL_CODE2 = N'CREATE VIEW [View_$1] WITH ENCRYPTION AS SELECT $2 FROM [$3] WHERE 1=1',
@SQL_CODE3 = N'GRANT SELECT ON [View_$1] TO PUBLIC',
@SQL_COLUMN_STR = N'DECLARE SEL_COLUMN_CS CURSOR FOR '
+ N' SELECT CAST(Column_Name AS NVARCHAR) AS CName, '
+ N' CAST(CASE WHEN ([Description] IS NOT NULL AND [Description] <> '''') THEN [Description] '
+ N' ELSE (CASE WHEN $2 = 1 THEN [Column_Name] ELSE '''' END) '
+ N' END AS NVARCHAR) AS VName, '
+ N' CAST(Type AS NVARCHAR) AS Type '
+ N' FROM FN_GetTableStructure('''+'$1'+''')',
@FIELD_STR --= N' CASE WHEN UPPER(LTRIM(RTRIM(''$T''))) = ''FLOAT'' '
--+ N' THEN CAST($1 AS DECIMAL(18,6)) '
--+ N' WHEN UPPER(LTRIM(RTRIM(''$T''))) = ''NVARCHAR'' '
--+ N' THEN CAST($1 AS VARCHAR(8000)) '
--+ N' ELSE [$1] '
--+ N' END AS [$2] '
= N' [$1] AS [$2] ',
@FLOAT_FIELD_STR = N'CAST([$1] AS DECIMAL(18,6)) AS [$2]'

DECLARE SEL_TABLE_CS CURSOR FOR
SELECT O.Name AS TableName,
CAST(CASE WHEN D.Value IS NOT NULL AND D.Value <> '' THEN D.Value
ELSE (CASE WHEN @TFlag = 1 THEN O.Name ELSE '' END)
END AS NVARCHAR) AS ViewName
FROM sysobjects O LEFT OUTER JOIN fn_GetTabDescription(default) AS D ON O.Name = D.ObjName
WHERE (O.Name = @TableName OR @TableName IS NULL OR @TableName = '')
AND D.Name = 'MS_Description' AND O.Type = 'U'
ORDER BY 2 DESC
OPEN SEL_TABLE_CS
FETCH NEXT FROM SEL_TABLE_CS INTO @TableName,@ViewName
WHILE (@@FETCH_STATUS = 0) BEGIN
IF @ViewName IS NOT NULL OR @ViewName <> '' BEGIN
SELECT @SQL_STR = REPLACE(REPLACE(@SQL_COLUMN_STR,'$1',@TableName),'$2',@FFlag)
PRINT @SQL_STR
EXECUTE SP_EXECUTESQL @SQL_STR
OPEN SEL_COLUMN_CS
FETCH NEXT FROM SEL_COLUMN_CS INTO @CName,@VName,@Type
SELECT @SEL_FIELD = ''
WHILE (@@FETCH_STATUS = 0) BEGIN
IF @VName IS NOT NULL AND @VName <> '' BEGIN
--IF UPPER(LTRIM(RTRIM(@Type))) = 'FLOAT' BEGIN
-- IF @SEL_FIELD IS NOT NULL AND @SEL_FIELD <> '' SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+' ,'
-- SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+LTRIM(RTRIM(REPLACE(REPLACE(@FLOAT_FIELD_STR,'$1',@CName),'$2',@VName)))
--END ELSE BEGIN
IF @SEL_FIELD IS NOT NULL AND @SEL_FIELD <> '' SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+' ,'
--SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@FIELD_STR,'$1',@CName),'$2',@VName),'$T',@Type)))
SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+LTRIM(RTRIM(REPLACE(REPLACE(@FIELD_STR,'$1',@CName),'$2',@VName)))
--END
END
FETCH NEXT FROM SEL_COLUMN_CS INTO @CName,@VName,@Type
END
CLOSE SEL_COLUMN_CS
DEALLOCATE SEL_COLUMN_CS

IF @SEL_FIELD IS NOT NULL AND @SEL_FIELD <> '' BEGIN
SELECT @SQL_STR = REPLACE(LTRIM(RTRIM(@SQL_CODE1)),'$1',@ViewName)
PRINT @SQL_STR
EXECUTE SP_EXECUTESQL @SQL_STR

SELECT @SQL_STR = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@SQL_CODE2)),'$1',@ViewName),'$2',@SEL_FIELD),'$3',@TableName)
PRINT @SQL_STR --可能超过 8000
EXECUTE SP_EXECUTESQL @SQL_STR

SELECT @SQL_STR = REPLACE(LTRIM(RTRIM(@SQL_CODE3)),'$1',@ViewName)
PRINT @SQL_STR
EXECUTE SP_EXECUTESQL @SQL_STR
END
END
FETCH NEXT FROM SEL_TABLE_CS INTO @TableName,@ViewName
END
CLOSE SEL_TABLE_CS
DEALLOCATE SEL_TABLE_CS
END
GO
GRANT ALL ON ITP_CREATE_VIEW TO PUBLIC
GO

[devilwind]

运行这个函数后上述的过程才能正确运行，

/*读取表结构*/

IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'FN_GetTableStructure' AND Type = 'TF') AND SUSER_SNAME() = 'sa'
DROP FUNCTION FN_GetTableStructure
GO
CREATE FUNCTION FN_GetTableStructure(
@TabName sysname /*表名称*/
)
RETURNS @RT TABLE(
[ID] INTEGER IDENTITY(1,1),
[Column_name] sysname,
[Description] sql_variant,
[Type] sql_variant,
[Computed] sql_variant,
[Length] sql_variant,
[Prec] sql_variant,
[Scale] sql_variant,
[Nullable] sql_variant,
[TrimTrailingBlanks] sql_variant,
[FixedLenNullInSource] sql_variant,
[Collation] sql_variant)
AS
BEGIN
DECLARE @numtypes NVARCHAR(80)
DECLARE @TMP TABLE( [ID] INTEGER IDENTITY(1,1),
[FID] INTEGER,
[DN] sql_variant
)
select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'

INSERT INTO @RT([Column_name] ,
[Description] ,
[Type] ,
[Computed] ,
[Length] ,
[Prec] ,
[Scale] ,
[Nullable] ,
[TrimTrailingBlanks] ,
[FixedLenNullInSource] ,
[Collation] )
select
'Column_name' = C.name,
'Description' = V.Value,
'Type' = type_name(C.xusertype),
'Computed' = case when C.iscomputed = 0 then 'no' else 'yes' end,
'Length' = convert(int, C.length),
'Prec' = case when charindex(type_name(C.xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(C.id, C.name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(C.xtype), @numtypes) > 0
then convert(char(5),OdbcScale(C.xtype,xscale))
else ' ' end,
'Nullable' = case when isnullable = 0 then 'no' else 'yes' end,
'TrimTrailingBlanks' = case ColumnProperty(2099048, C.name, 'UsesAnsiTrim')
when 1 then 'no'
when 0 then 'yes'
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(C.xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When C.status & 0x20 = 0 Then 'no'
Else 'yes' END,
'Collation' = C.collation
from syscolumns AS C
LEFT OUTER JOIN
(
SELECT *
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TabName, 'column', default)
) AS V
ON C.name = V.ObjName
where C.id = OBJECT_ID(@TabName) and C.number = 0 order by C.colid

INSERT INTO @TMP([FID],[DN])
SELECT R.ID,R.[Description]
FROM @RT AS R INNER JOIN
(SELECT [Description]
FROM @RT
WHERE [Description] IS NOT NULL
AND [Description] <> ''
GROUP BY [Description]
HAVING count(1) > 1
) AS A
ON R.[Description] = A.[Description]
WHERE R.[Description] IS NOT NULL
AND R.[Description] <> ''
ORDER BY 2,1

UPDATE @RT
SET [Description] = LTRIM(RTRIM(CAST([Description] AS NVARCHAR)))
+ CAST(A.SeqNo AS NVARCHAR)
FROM (SELECT ID + 1 - ISNULL((SELECT MIN(A.ID) FROM @TMP AS A WHERE A.DN = DN),0) AS SeqNo,
FID
FROM @TMP
WHERE 1=1 ) AS A
WHERE A.FID = ID

RETURN
END
GO
GRANT ALL ON FN_GetTableStructure TO PUBLIC
GO

[nobelpu]

可以用TEXT,NTEXT替代VARCHAR,NVARCHAR解决不能超过8000字符的问题， 但是
TEXT,NTEXT,IMAGE 不能定义成本地变量，所以，只能建立临时表，字段类型定义为
TEXT或NTEXT。用临时表存储超过8000字符的信息。如存储的是SQL指令，需要执行，
则可利用文本文件中转，将存储的SQL指令利用BCP导出至文本，再利用ISQL执行文本中的
指令。需要利用SQL指令返回数据集时，可先利用SQL指令采取上述方法建立视图，再查询视图得到所需的结果集。