我想问一下在存储过程里，什么类型的字符长度可以大于4000？

[uxuan]

我想问一下在存储过程里，什么类型的字符长度可以大于4000？
我定义的数据类型nvarchar,varchar都只有4000！

[uxuan]

up

[zjcxc]

nvarchar(4000)
varchar(8000)

[datland]

char

固定長度的非 Unicode 字元資料，最大長度為 8,000 個字元。

varchar

可變長度的非 Unicode 資料，最大長度為 8,000 個字元。

text

可變長度的非 Unicode 資料，最大長度為 2^31 - 1 (2,147,483,647) 個字元。

Unicode 字元字串
nchar

固定長度的非 Unicode 資料，最大長度為 4,000 個字元。

nvarchar

可變長度的 Unicode 資料，最大長度為 4,000 個字元。sysname 是一個系統支援的使用者自訂資料型別，為 nvarchar(128) 的同義資料表，用來參考資料庫物件名稱。

ntext

可變長度的 Unicode 資料，最大長度為 2^30 - 1 (1,073,741,823) 個字元。

[GoldShield]

这么详细

[rouqu]

text类型不被存储过程支持 但是可以变通往SP中传入长度大于8000的字符串参数

[uxuan]

先谢谢大家
varchar(8000)，只能存储4000字符
在存储过程里
declare @T ntext
报错：错误2739：对于局部变量text,ntext 数据类型无效
declare @T ntext（5000）
报错：错误2716：不能对ntext指定宽度
望指教

[zjcxc]

varchar(8000)，只能存储4000字符 -----写出你的代码

declare @T ntext --sql没有对text/ntext变量的操作语句

[uxuan]

CREATE PROCEDURE ProTJ_YCTJ @Lev varchar(100) AS
declare @strDM varchar(10),@strMC varchar(10)
declare @sSQL varchar(8000)
declare @BaseSQL nvarchar(500),@ZJSQL nvarchar(1000), @YLLSQL nvarchar(1000),@ZLLSQL nvarchar(1000),@JSLSQL nvarchar(1000),@CSLSQL nvarchar(1000), @GSLSQL nvarchar(1000)
IF @Lev='工作站'
BEGIN
Set @BaseSQL="Select XZDM AS MCPXM, XZMC AS MC," +
"Case When LMSYQ='1-国有' then 0 " +
"When LMSYQ='2-集体' then 1 " +
"When LMSYQ='3-民营' then 2 " +
"When LMSYQ='4-外资' then 3 " +
"When LMSYQ='5-其他' then 4 " +
"End As LMSYQPXM,LMSYQ As LMSYQ, " +
"Case When YLZ='31-短轮伐用材' then 1 " +
"When YLZ='32-速丰用材林' then 2 " +
"When YLZ='33-一般用材林' then 3 " +
"End As YLZPXM,YLZ As YLZ, " +
"Case When YSDL=' 1-杉柏类' then 0 " +
"When YSDL=' 2-马尾松' then 1 " +
"When YSDL=' 3-国外松' then 2 " +
"When YSDL=' 4-软阔类' then 3 " +
"When YSDL=' 5-硬阔类' then 4 " +
"When YSDL=' 6-竹 类' then 5 " +
"End As YSDLPXM,YSDL, "
Set @ZJSQL= @BaseSQL + " Sum(XBMJ) As ZMJ,Sum(HLMXJ) As ZXJ From Xbb_New Where LZH='3-用材林' Group By XZDM,XZMC,LMSYQ,YLZ,YSDL"
Set @YLLSQL = @BaseSQL + "Sum(XBMJ) As YLLMJ,Sum(HLMXJ) As YLLXJ From Xbb_New Where LZH='3-用材林' And LZ='1-幼龄林' Group By XZDM,XZMC,LMSYQ,YLZ,YSDL"
Set @ZLLSQL = @BaseSQL + "Sum(XBMJ) As ZLLMJ,Sum(HLMXJ) As ZLLXJ From Xbb_New Where LZH='3-用材林' And LZ='2-中龄林' Group By XZDM,XZMC,LMSYQ,YLZ,YSDL"
Set @JSLSQL = @BaseSQL + "Sum(XBMJ) As JSLMJ,Sum(HLMXJ) As JSLXJ From Xbb_New Where LZH='3-用材林' And LZ='3-近熟林' Group By XZDM,XZMC,LMSYQ,YLZ,YSDL"
Set @CSLSQL = @BaseSQL + "Sum(XBMJ) As CSLMJ,Sum(HLMXJ) As CSLXJ From Xbb_New Where LZH='3-用材林' And LZ='4-成熟林' Group By XZDM,XZMC,LMSYQ,YLZ,YSDL"
Set @GSLSQL = @BaseSQL + "Sum(XBMJ) As GSLMJ,Sum(HLMXJ) As GSLXJ From Xbb_New Where LZH='3-用材林' And LZ='5-过熟林' Group By XZDM,XZMC,LMSYQ,YLZ,YSDL"
Set @sSQL = "Select A.*,B.YLLMJ,B.YLLXJ From (" + @ZJSQL + ") As A Left Join " + "(" + @YLLSQL + ") As B On A.MCPXM=B.MCPXM And A.LMSYQPXM=B.LMSYQPXM And A.YLZPXM=B.YLZPXM And A.YSDLPXM=B.YSDLPXM"
Set @sSQL = "Select A.*,B.ZLLMJ,B.ZLLXJ From (" + @sSQL + ") As A Left Join " + "(" + @ZLLSQL + ") As B On A.MCPXM=B.MCPXM And A.LMSYQPXM=B.LMSYQPXM And A.YLZPXM=B.YLZPXM And A.YSDLPXM=B.YSDLPXM"
Set @sSQL = "Select A.*,B.JSLMJ,B.JSLXJ From (" + @sSQL + ") As A Left Join " + "(" + @JSLSQL + ") As B On A.MCPXM=B.MCPXM And A.LMSYQPXM=B.LMSYQPXM And A.YLZPXM=B.YLZPXM And A.YSDLPXM=B.YSDLPXM"
Set @sSQL = "Select A.*,B.CSLMJ,B.CSLXJ From (" + @sSQL + ") As A Left Join " + "(" + @CSLSQL + ") As B On A.MCPXM=B.MCPXM And A.LMSYQPXM=B.LMSYQPXM And A.YLZPXM=B.YLZPXM And A.YSDLPXM=B.YSDLPXM"
Set @sSQL = "Select A.*,B.GSLMJ,B.GSLXJ From (" + @sSQL + ") As A Left Join " + "(" + @GSLSQL + ") As B On A.MCPXM=B.MCPXM And A.LMSYQPXM=B.LMSYQPXM And A.YLZPXM=B.YLZPXM And A.YSDLPXM=B.YSDLPXM"

exec (@sSQL)
print @ssql
print len(@ssql)
END
GO


print len(@ssql)----结果长度为4000

[uxuan]

邹 建为什么不回答了呢，不过如此嘛

[icedut]

楼住的语句太夸张了

这得执行多长时间啊

[uxuan]

up

[wuxuanwx]

是的，varchar(8000)，只能存储4000字符

[wuxuanwx]

是的，varchar(8000)，只能存储4000字符

[cer]

text