nvarchar(max) 数据类型问题

fyming 2009-06-19 01:23:01
DECLARE
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX)

SET @SQL2=N'WHERE 1>0 '

SET @SQL1=N'……一大堆字符……'+ @SQL2
==========================================

运行结果：
消息 402，级别 16，状态 1，过程 e_SignReport，第 387 行
数据类型 ntext 和 nvarchar(max) 在 add 运算符中不兼容。

说明：这是存储过程中的一段代码，如果不加@SQL2就不会有错。系统好象是把@SQL1当成了NTEXT了，但我之前定义了@SQL1 NVARCHAR(MAX)呀。

如何解决？谢谢
...全文
969 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
jtaocs 2009-12-18
  • 打赏
  • 举报
 回复
写的不错
ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复 
declare  @str1  varchar(max) 

select  @str1  =  replicate( CAST('1' as nvarchar(max)),8006) 

select  len(@str1) --  8000 



--------------------

8006



(1 行受影响)
ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复
但是什么情况下会转换为ntext倒没遇到过。
ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 fyming 的回复:]
declare @str1 varchar(max)
select @str1 = replicate( '1 ',8006)
select len(@str1) -- 8000
[/Quote]这里有个默认类型转换问题。
varchar(max)+varchar(max)=》varchar(max)

varchar(max)+varchar(x)=》varchar(8000)
fyming 2009-06-19
  • 打赏
  • 举报
 回复
最后我找到了一个变通的办法：
即对@SQL变量进行拼接之前，先显式转换一下数据类型， SET @SQL=CONVERT(NVARCHAR(MAX),@SQL)
然后再用
SET @SQL=@SQL + @Where+@FieldOrder+N' SET @Rows=@@ROWCOUNT'

EXEC SP_EXECUTESQL @SQL,N'@Rows INT OUTPUT',@Rows OUTPUT
============================================================

具体的问题这然可以解决，但我还是没搞清楚：为什么之前我明明已经定义@SQL为NVARCHAR（MAX），但在后面还要再转换一下数据类型，否则就会被SQL2005默认为NTEXT？

又做了一些测试，发现对于NVARCHAR（MAX）数据类型的变量，接收外部程序传入的数据确实没有问题，LEN出现的可能有1W多都没有问题，但做字符串拼接时，可能会出错。

恳请高手指点一下！
play7788 2009-06-19
  • 打赏
  • 举报
 回复 


DECLARE 

@SQL1 NVARCHAR(MAX), 

@SQL2 NVARCHAR(MAX),

@SQL3 NVARCHAR(MAX)



SET @SQL2=N'WHERE 1>0 ' 

SET @SQL1=N'……一大堆字符……'

SET @SQL3=@SQL1+@SQL2


那你这样试试。
fyming 2009-06-19
  • 打赏
  • 举报
 回复

刚才GOOGLE了一下，翻出(晓)风残月的一段代码（http://topic.csdn.net/u/20071215/10/50932d75-9938-418b-8ffa-71f995a50c5a.html）如下：

-- varchar 中max用法

-- 测试一普通的varchar
declare @str1 varchar(4001)
declare @str2 varchar(4001)
select @str1 = replicate( '1 ',4001)
select @str2 = replicate( '2 ',4001)
select len(@str1 + @str2) -- 8000
print @str1
print @str2

-- 测试二 max的varchar
declare @str1 varchar(4001)
declare @str2 varchar(4001)
select @str1 = replicate( '1 ',4001)
select @str2 = replicate( '2 ',4001)
select len(convert(varchar(max),@str1)+@str2) -- 8002
select len(convert(varchar(max),@str1))

--测试三
declare @str1 varchar(max)
select @str1 = replicate( '1 ',8006)
select len(@str1) -- 8000

==================================================
对于测试三，为什么select len(@str1)是7999而不是8006？这和我的问题有点象，但即使是原贴，也好象没有一个最后的答案。
playwarcraft 2009-06-19
  • 打赏
  • 举报
 回复

fyming 2009-06-19
  • 打赏
  • 举报
 回复
还是没有答案，SQL2005还是把@sql当作NTEXT类型了，请其他兄弟也帮忙测试一下，这种问题，你们没有遇到过吗？
playwarcraft 2009-06-19
  • 打赏
  • 举报
 回复
--copy你的程序，在我機器上，註釋掉最後一行，可以跑
--結果如下 （@sql 的len =9303)


/*

SELECT  

a.lngItemAcceptID,a.lngBusinessTypeID,a.strItemAccepNo, 

a.lngCustomerID,a.lngEmployeeID,a.bytType,a.bytState, a.lngUserID 



b.strBusinessTypeName, 

c.strCustomerCode,c.strCustomerName,c.strCustomerAlias, 

d.strEmployeeCode,d.strEmployeeName, 

e.strUserCode,e.strUserName, 

f.lngItemAcceptDetailID, 

f.dateActivityDate1,f.dateActivityDate2,f.dateActivityDate3,f.dateActivityDate4,f.dateActivityDate5,f.dateActivityDate6, 

f.lngDefine1,f.lngDefine2,f.lngDefine3,f.lngDefine4,f.lngDefine5,f.lngDefine6, 

f.dblDefin1,f.dblDefin2,f.dblDefin3,f.dblDefin4,f.dblDefin5,f.dblDefin6, 

f.strDefine1,f.strDefine2,f.strDefine3,f.strDefine4,f.strDefine5,f.strDefine6, 

.......................



*/

fyming 2009-06-19
  • 打赏
  • 举报
 回复
1、TO ai_li7758521
真是怪了，同样的代码，为什么你可以而我就不行呢？

2、TO 乳沟
即使注释掉最后一句也不行。语法分析通过，执行不了。


是不是和SQL的哪里的设置有关呢？
playwarcraft 2009-06-19
  • 打赏
  • 举报
 回复
你把最後一句註釋掉 EXEC SP_EXECUTESQL @SQL,N'@Rows INT OUTPUT',@Rows OUTPUT
看看能不能跑？
ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复 
--处理@FieldOrder 

IF @FieldOrder='' SET @FieldOrder=' a.strItemAccepNo ' 

SET @FieldOrder=' ORDER BY '+LTRIM(@FieldOrder) 



--处理@Where 

SET @Where=' WHERE (1>0 ' +@Where+')'

--处理查询字符串 

SET @SQL='SELECT '+@FieldShow+' FROM ItemAccept a 

LEFT JOIN 

(SELECT lngBusinessTypeID,strBusinessTypeName FROM BusinessType) b 

ON a.lngBusinessTypeID=b.lngBusinessTypeID 



LEFT JOIN 

(SELECT lngCustomerID,strCustomerCode,strCustomerName,strCustomerAlias FROM Customer) c 

ON a.lngCustomerID=c.lngCustomerID 



LEFT JOIN 

(SELECT lngEmployeeID,strEmployeeCode,strEmployeeName FROM Employee) d 

ON a.lngEmployeeID=d.lngEmployeeID 



LEFT JOIN 

(SELECT lngUserID,strUserCode,strUserName FROM Users) AS e  

ON a.lngUserID=e.lngUserID 



LEFT JOIN 

(SELECT lngItemAcceptDetailID, strItemAccepNo, 

dateActivityDate1, dateActivityDate2, dateActivityDate3, 

dateActivityDate4, dateActivityDate5, dateActivityDate6, 

lngDefine1, lngDefine2, lngDefine3, lngDefine4, lngDefine5, lngDefine6, 

dblDefin1, dblDefin2, dblDefin3, dblDefin4, dblDefin5, dblDefin6, 

strDefine1, strDefine2, strDefine3, strDefine4, strDefine5, strDefine6 

FROM ItemAcceptDetail) AS f  

ON a.strItemAccepNo=f.strItemAccepNo 



LEFT JOIN 

(SELECT lngItemAuditID, lngItemAcceptID, bytType AS bytType_ItemAudit, 

strAuditIdea, lngUserID AS lngUserID_ItemAudit FROM ItemAudit) AS g  

ON a.lngItemAcceptID=g.lngItemAcceptID 



LEFT JOIN 

(SELECT a.lngItemRegisterID, a.lngItemAcceptID, a.lngGetReportNOID, 

a.lngEmployeeID AS lngEmployeeID_ItemRegister, a.strEmpID,a.strEmpName, 

dateActivityDate1 AS dateActivityDate1_ItemRegister, 

dateActivityDate2 AS dateActivityDate2_ItemRegister, 

dateActivityDate3 AS dateActivityDate3_ItemRegister, 

dateActivityDate4 AS dateActivityDate4_ItemRegister, 

dateActivityDate5 AS dateActivityDate5_ItemRegister, 

a.strRiskExplain, a.dblRiskRate, a.lngDays, a.lnghours, a.lngUserID AS lngUserID_ItemRegister, 



b.lngReportTypeID AS lngReportTypeID_GetReportNO, strReportNo,b.lngEmployeeID_GetReportNO, 

b.strReportTypeName AS strReportTypeName_GetReportNO, 

b.strEmployeeCode_GetReportNO, 

b.strEmployeeName_GetReportNO, 

b.strEmployeeAlias_GetReportNO, 

c.strEmployeeCode AS strEmployeeCode_ItemRegister, 

c.strEmployeeName AS strEmployeeName_ItemRegister, 

c.strEmployeeAlias AS strEmployeeAlias_ItemRegister 



FROM ItemRegister a 



LEFT JOIN 

(SELECT lngGetReportNOID, a.lngReportTypeID, a.lngBusinessTypeID, strReportNo, 

a.lngEmployeeID AS lngEmployeeID_GetReportNO, a.lngCustomerID, 

b.strReportTypeName, b.strPrefix_ReportType,b.bytType_ReportType, 

c.strBusinessTypeName,c.strPrefix_BusinessType, 

d.strEmployeeCode AS strEmployeeCode_GetReportNO, 

d.strEmployeeName AS strEmployeeName_GetReportNO, 

d.strEmployeeAlias AS strEmployeeAlias_GetReportNO, 

e.strCustomerCode AS strCustomerCode_GetReportNO, 

e.strCustomerName AS strCustomerName_GetReportNO, 

e.strCustomerAlias AS strCustomerAlias_GetReportNO 



FROM GetReportNO a 

LEFT JOIN 

(SELECT lngReportTypeID, strReportTypeName, strPrefix AS strPrefix_ReportType, 

bytType AS bytType_ReportType FROM ReportType)b 

ON a.lngReportTypeID=b.lngReportTypeID 



LEFT JOIN 

(SELECT lngBusinessTypeID, strBusinessTypeName, strPrefix AS strPrefix_BusinessType FROM BusinessType)c 

ON a.lngBusinessTypeID=c.lngBusinessTypeID 



LEFT JOIN 

(SELECT lngEmployeeID, strEmployeeCode, strEmployeeName,strEmployeeAlias FROM Employee)d 

ON a.lngEmployeeID=d.lngEmployeeID 



LEFT JOIN 

(SELECT lngCustomerID, strCustomerCode, strCustomerName, strCustomerAlias FROM Customer)e 

ON a.lngCustomerID=e.lngCustomerID)b 

ON a.lngGetReportNOID=b.lngGetReportNOID 



LEFT JOIN 

(SELECT lngEmployeeID, strEmployeeCode, strEmployeeName, strEmployeeAlias FROM Employee)c 

ON a.lngEmployeeID=c.lngEmployeeID) AS h  

ON a.lngItemAcceptID=h.lngItemAcceptID 



LEFT JOIN(SELECT 

a.lngItemCheckID AS lngItemCheckID_ItemCheck1, 

a.lngItemAcceptID, 

a.bytCheckType AS bytCheckType1, 

a.dateActivityDate AS dateActivityDate_ItemCheck1, 

a.strCheckIdea AS strCheckIdea_ItemCheck1, 

a.dateEditTime AS dateEditTime_ItemCheck1, 

a.lngUserID AS lngUserID_ItemCheck1, 

b.strUserCode AS strUserCode_ItemCheck1, 

b.strUserName AS strUserName_ItemCheck1 



FROM ItemCheck a 



LEFT JOIN(SELECT lngUserID,strUserCode,strUserName FROM Users)b 

ON a.lngUserID=b.lngUserID 

WHERE  bytCheckType=0)i 

ON a.lngItemAcceptID=i.lngItemAcceptID 



LEFT JOIN(SELECT 

a.lngItemCheckID AS lngItemCheckID_ItemCheck2, 

a.lngItemAcceptID, 

a.bytCheckType AS bytCheckType2, 

a.dateActivityDate AS dateActivityDate_ItemCheck2, 

a.strCheckIdea AS strCheckIdea_ItemCheck2, 

a.dateEditTime AS dateEditTime_ItemCheck2, 

a.lngUserID AS lngUserID_ItemCheck2, 

b.strUserCode AS strUserCode_ItemCheck2, 

b.strUserName AS strUserName_ItemCheck2 



FROM ItemCheck a 



LEFT JOIN(SELECT lngUserID,strUserCode,strUserName FROM Users)b 

ON a.lngUserID=b.lngUserID 

WHERE  bytCheckType=1)j 

ON a.lngItemAcceptID=j.lngItemAcceptID 



LEFT JOIN(SELECT 

a.lngItemCheckID AS lngItemCheckID_ItemCheck3, 

a.lngItemAcceptID, 

a.bytCheckType AS bytCheckType3, 

a.dateActivityDate AS dateActivityDate_ItemCheck3, 

a.strCheckIdea AS strCheckIdea_ItemCheck3, 

a.dateEditTime AS dateEditTime_ItemCheck3, 

a.lngUserID AS lngUserID_ItemCheck3, 

b.strUserCode AS strUserCode_ItemCheck3, 

b.strUserName AS strUserName_ItemCheck3 



FROM ItemCheck a 



LEFT JOIN(SELECT lngUserID,strUserCode,strUserName FROM Users)b 

ON a.lngUserID=b.lngUserID 

WHERE  bytCheckType=2)k 

ON a.lngItemAcceptID=k.lngItemAcceptID 



LEFT JOIN(SELECT 

a.lngSignReportID,a.lngItemAcceptID,a.lngIdeaTypeID,a.dateActivityDate, 

a.strInvoiceNo,a.dblFactManHour1,a.dblFactManHour2,a.lngReportNum, 

a.strDeliverID1,a.strDeliverName1,a.strDeliverID2,a.strDeliverName2, 

a.strComment,a.dateEditTime,a.lngUserID, 

a.lngDefine1,a.lngDefine2,a.lngDefine3,a.lngDefine4,a.lngDefine5, 

a.lngDefine6,a.lngDefine7,a.lngDefine8,a.lngDefine9,a.lngDefine10, 

a.dblDefin1,a.dblDefin2,a.dblDefin3,a.dblDefin4,a.dblDefin5, 

a.dblDefin6,a.dblDefin7,a.dblDefin8,a.dblDefin9,a.dblDefin10, 

a.strDefine1,a.strDefine2,a.strDefine3,a.strDefine4,a.strDefine5, 

a.strDefine6,a.strDefine7,a.strDefine8,a.strDefine9,a.strDefine10, 

b.strIdeaTypeName, 

c.strUserCode_SignReport,c.strUserName_SignReport 



FROM SignReport a 



LEFT JOIN(SELECT lngIdeaTypeID, strIdeaTypeName FROM IdeaType)b 

ON a.lngIdeaTypeID=b.lngIdeaTypeID 



LEFT JOIN(SELECT lngUserID, 

strUserCode AS strUserCode_SignReport, 

strUserName AS strUserName_SignReport FROM Users)c 

ON a.lngUserID=c.lngUserID)l 

ON a.lngItemAcceptID=l.lngItemAcceptID '+@Where+@FieldOrder+ ' SET @Rows=@@ROWCOUNT'



select @SQL 



--end

--EXEC SP_EXECUTESQL @SQL,N'@Rows INT OUTPUT',@Rows OUTPUT
ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复
测试无误：
--ALTER PROCEDURE [dbo].[e_SignReport] 

DECLARE

@v INT, 

@FieldShow  NVARCHAR(MAX),--=N'',  

@FieldOrder NVARCHAR(MAX),--=N'',  

@Where NVARCHAR(MAX)--=N'' , 



select 

@v=10000

,@FieldShow=''

,@FieldOrder='@FieldOrder@FieldOrder@FieldOrder@FieldOrder'

,@Where='@Where@Where@Where@Where@Where'





----@Rows INT=NULL OUTPUT

--,@lngErrCode INT=NULL OUTPUT, 

--@lngErrCode_1 INT=NULL OUTPUT 

--------------------------------------------------------------- 



--WITH ENCRYPTION 

--

--AS 

--

--SET NOCOUNT ON 



DECLARE 

@SQL NVARCHAR(MAX), 

@ID INT, 

@isUsed BIT, 

@dateActivityDate SMALLDATETIME, 

@strPrefix NVARCHAR(30) 



--IF @v=5 

--BEGIN 

--SELECT @lngErrCode=0 



--处理@FieldShow 

IF @FieldShow=''  

SET @FieldShow=' 

a.lngItemAcceptID,a.lngBusinessTypeID,a.strItemAccepNo, 

a.lngCustomerID,a.lngEmployeeID,a.bytType,a.bytState, a.lngUserID 



b.strBusinessTypeName, 

c.strCustomerCode,c.strCustomerName,c.strCustomerAlias, 

d.strEmployeeCode,d.strEmployeeName, 

e.strUserCode,e.strUserName, 

f.lngItemAcceptDetailID, 

f.dateActivityDate1,f.dateActivityDate2,f.dateActivityDate3,f.dateActivityDate4,f.dateActivityDate5,f.dateActivityDate6, 

f.lngDefine1,f.lngDefine2,f.lngDefine3,f.lngDefine4,f.lngDefine5,f.lngDefine6, 

f.dblDefin1,f.dblDefin2,f.dblDefin3,f.dblDefin4,f.dblDefin5,f.dblDefin6, 

f.strDefine1,f.strDefine2,f.strDefine3,f.strDefine4,f.strDefine5,f.strDefine6, 



g.lngItemAuditID,g.bytType_ItemAudit,g.strAuditIdea,g.lngUserID_ItemAudit, 



h.lngItemRegisterID,h.lngGetReportNOID, 

h.lngEmployeeID_ItemRegister,h.strEmpID,h.strEmpName, 

h.dateActivityDate1_ItemRegister, 

h.dateActivityDate2_ItemRegister, 

h.dateActivityDate3_ItemRegister, 

h.dateActivityDate4_ItemRegister, 

h.dateActivityDate5_ItemRegister, 

h.strRiskExplain,h.dblRiskRate,h.lngDays,h.lnghours,h.lngUserID_ItemRegister, 

h.lngReportTypeID_GetReportNO,h.strReportNo,h.lngEmployeeID_GetReportNO, 

h.strReportTypeName_GetReportNO, 

h.strEmployeeCode_GetReportNO,h.strEmployeeName_GetReportNO,h.strEmployeeAlias_GetReportNO, 

h.strEmployeeCode_ItemRegister,h.strEmployeeName_ItemRegister,h.strEmployeeAlias_ItemRegister, 



i.lngItemCheckID_ItemCheck1,i.bytCheckType1, 

i.dateActivityDate_ItemCheck1, i.strCheckIdea_ItemCheck1, 

i.dateEditTime_ItemCheck1, 

i.lngUserID_ItemCheck1, 

i.strUserCode_ItemCheck1,i.strUserName_ItemCheck1, 



j.lngItemCheckID_ItemCheck2,j.bytCheckType2, 

j.dateActivityDate_ItemCheck2, j.strCheckIdea_ItemCheck2, 

j.dateEditTime_ItemCheck2, 

j.lngUserID_ItemCheck2, 

j.strUserCode_ItemCheck2,j.strUserName_ItemCheck2, 



k.lngItemCheckID_ItemCheck3,k.bytCheckType3, 

k.dateActivityDate_ItemCheck3, k.strCheckIdea_ItemCheck3, 

k.dateEditTime_ItemCheck3, 

k.lngUserID_ItemCheck3, 

k.strUserCode_ItemCheck3,k.strUserName_ItemCheck3, 



l.lngSignReportID,l.lngItemAcceptID,l.lngIdeaTypeID,l.dateActivityDate, 

l.strInvoiceNo,l.dblFactManHour1,l.dblFactManHour2,l.lngReportNum, 

l.strDeliverID1,l.strDeliverName1,l.strDeliverID2,l.strDeliverName2, 

l.strComment,l.dateEditTime,l.lngUserID, 

l.lngDefine1,l.lngDefine2,l.lngDefine3,l.lngDefine4,l.lngDefine5, 

l.lngDefine6,l.lngDefine7,l.lngDefine8,l.lngDefine9,l.lngDefine10, 

l.dblDefin1,l.dblDefin2,l.dblDefin3,l.dblDefin4,l.dblDefin5, 

l.dblDefin6,l.dblDefin7,l.dblDefin8,l.dblDefin9,l.dblDefin10, 

l.strDefine1,l.strDefine2,l.strDefine3,l.strDefine4,l.strDefine5, 

l.strDefine6,l.strDefine7,l.strDefine8,l.strDefine9,l.strDefine10, 

l.strIdeaTypeName, 

l.strUserCode_SignReport,l.strUserName_SignReport '
fyming 2009-06-19
  • 打赏
  • 举报
 回复


--处理查询字符串
SET @SQL=N'SELECT '+@FieldShow+N' FROM ItemAccept a
LEFT JOIN
(SELECT lngBusinessTypeID,strBusinessTypeName FROM BusinessType) b
ON a.lngBusinessTypeID=b.lngBusinessTypeID

LEFT JOIN
(SELECT lngCustomerID,strCustomerCode,strCustomerName,strCustomerAlias FROM Customer) c
ON a.lngCustomerID=c.lngCustomerID

LEFT JOIN
(SELECT lngEmployeeID,strEmployeeCode,strEmployeeName FROM Employee) d
ON a.lngEmployeeID=d.lngEmployeeID

LEFT JOIN
(SELECT lngUserID,strUserCode,strUserName FROM Users) AS e
ON a.lngUserID=e.lngUserID

LEFT JOIN
(SELECT lngItemAcceptDetailID, strItemAccepNo,
dateActivityDate1, dateActivityDate2, dateActivityDate3,
dateActivityDate4, dateActivityDate5, dateActivityDate6,
lngDefine1, lngDefine2, lngDefine3, lngDefine4, lngDefine5, lngDefine6,
dblDefin1, dblDefin2, dblDefin3, dblDefin4, dblDefin5, dblDefin6,
strDefine1, strDefine2, strDefine3, strDefine4, strDefine5, strDefine6
FROM ItemAcceptDetail) AS f
ON a.strItemAccepNo=f.strItemAccepNo

LEFT JOIN
(SELECT lngItemAuditID, lngItemAcceptID, bytType AS bytType_ItemAudit,
strAuditIdea, lngUserID AS lngUserID_ItemAudit FROM ItemAudit) AS g
ON a.lngItemAcceptID=g.lngItemAcceptID

LEFT JOIN
(SELECT a.lngItemRegisterID, a.lngItemAcceptID, a.lngGetReportNOID,
a.lngEmployeeID AS lngEmployeeID_ItemRegister, a.strEmpID,a.strEmpName,
dateActivityDate1 AS dateActivityDate1_ItemRegister,
dateActivityDate2 AS dateActivityDate2_ItemRegister,
dateActivityDate3 AS dateActivityDate3_ItemRegister,
dateActivityDate4 AS dateActivityDate4_ItemRegister,
dateActivityDate5 AS dateActivityDate5_ItemRegister,
a.strRiskExplain, a.dblRiskRate, a.lngDays, a.lnghours, a.lngUserID AS lngUserID_ItemRegister,

b.lngReportTypeID AS lngReportTypeID_GetReportNO, strReportNo,b.lngEmployeeID_GetReportNO,
b.strReportTypeName AS strReportTypeName_GetReportNO,
b.strEmployeeCode_GetReportNO,
b.strEmployeeName_GetReportNO,
b.strEmployeeAlias_GetReportNO,
c.strEmployeeCode AS strEmployeeCode_ItemRegister,
c.strEmployeeName AS strEmployeeName_ItemRegister,
c.strEmployeeAlias AS strEmployeeAlias_ItemRegister

FROM ItemRegister a

LEFT JOIN
(SELECT lngGetReportNOID, a.lngReportTypeID, a.lngBusinessTypeID, strReportNo,
a.lngEmployeeID AS lngEmployeeID_GetReportNO, a.lngCustomerID,
b.strReportTypeName, b.strPrefix_ReportType,b.bytType_ReportType,
c.strBusinessTypeName,c.strPrefix_BusinessType,
d.strEmployeeCode AS strEmployeeCode_GetReportNO,
d.strEmployeeName AS strEmployeeName_GetReportNO,
d.strEmployeeAlias AS strEmployeeAlias_GetReportNO,
e.strCustomerCode AS strCustomerCode_GetReportNO,
e.strCustomerName AS strCustomerName_GetReportNO,
e.strCustomerAlias AS strCustomerAlias_GetReportNO

FROM GetReportNO a
LEFT JOIN
(SELECT lngReportTypeID, strReportTypeName, strPrefix AS strPrefix_ReportType,
bytType AS bytType_ReportType FROM ReportType)b
ON a.lngReportTypeID=b.lngReportTypeID

LEFT JOIN
(SELECT lngBusinessTypeID, strBusinessTypeName, strPrefix AS strPrefix_BusinessType FROM BusinessType)c
ON a.lngBusinessTypeID=c.lngBusinessTypeID

LEFT JOIN
(SELECT lngEmployeeID, strEmployeeCode, strEmployeeName,strEmployeeAlias FROM Employee)d
ON a.lngEmployeeID=d.lngEmployeeID

LEFT JOIN
(SELECT lngCustomerID, strCustomerCode, strCustomerName, strCustomerAlias FROM Customer)e
ON a.lngCustomerID=e.lngCustomerID)b
ON a.lngGetReportNOID=b.lngGetReportNOID

LEFT JOIN
(SELECT lngEmployeeID, strEmployeeCode, strEmployeeName, strEmployeeAlias FROM Employee)c
ON a.lngEmployeeID=c.lngEmployeeID) AS h
ON a.lngItemAcceptID=h.lngItemAcceptID

LEFT JOIN(SELECT
a.lngItemCheckID AS lngItemCheckID_ItemCheck1,
a.lngItemAcceptID,
a.bytCheckType AS bytCheckType1,
a.dateActivityDate AS dateActivityDate_ItemCheck1,
a.strCheckIdea AS strCheckIdea_ItemCheck1,
a.dateEditTime AS dateEditTime_ItemCheck1,
a.lngUserID AS lngUserID_ItemCheck1,
b.strUserCode AS strUserCode_ItemCheck1,
b.strUserName AS strUserName_ItemCheck1

FROM ItemCheck a

LEFT JOIN(SELECT lngUserID,strUserCode,strUserName FROM Users)b
ON a.lngUserID=b.lngUserID
WHERE bytCheckType=0)i
ON a.lngItemAcceptID=i.lngItemAcceptID

LEFT JOIN(SELECT
a.lngItemCheckID AS lngItemCheckID_ItemCheck2,
a.lngItemAcceptID,
a.bytCheckType AS bytCheckType2,
a.dateActivityDate AS dateActivityDate_ItemCheck2,
a.strCheckIdea AS strCheckIdea_ItemCheck2,
a.dateEditTime AS dateEditTime_ItemCheck2,
a.lngUserID AS lngUserID_ItemCheck2,
b.strUserCode AS strUserCode_ItemCheck2,
b.strUserName AS strUserName_ItemCheck2

FROM ItemCheck a

LEFT JOIN(SELECT lngUserID,strUserCode,strUserName FROM Users)b
ON a.lngUserID=b.lngUserID
WHERE bytCheckType=1)j
ON a.lngItemAcceptID=j.lngItemAcceptID

LEFT JOIN(SELECT
a.lngItemCheckID AS lngItemCheckID_ItemCheck3,
a.lngItemAcceptID,
a.bytCheckType AS bytCheckType3,
a.dateActivityDate AS dateActivityDate_ItemCheck3,
a.strCheckIdea AS strCheckIdea_ItemCheck3,
a.dateEditTime AS dateEditTime_ItemCheck3,
a.lngUserID AS lngUserID_ItemCheck3,
b.strUserCode AS strUserCode_ItemCheck3,
b.strUserName AS strUserName_ItemCheck3

FROM ItemCheck a

LEFT JOIN(SELECT lngUserID,strUserCode,strUserName FROM Users)b
ON a.lngUserID=b.lngUserID
WHERE bytCheckType=2)k
ON a.lngItemAcceptID=k.lngItemAcceptID

LEFT JOIN(SELECT
a.lngSignReportID,a.lngItemAcceptID,a.lngIdeaTypeID,a.dateActivityDate,
a.strInvoiceNo,a.dblFactManHour1,a.dblFactManHour2,a.lngReportNum,
a.strDeliverID1,a.strDeliverName1,a.strDeliverID2,a.strDeliverName2,
a.strComment,a.dateEditTime,a.lngUserID,
a.lngDefine1,a.lngDefine2,a.lngDefine3,a.lngDefine4,a.lngDefine5,
a.lngDefine6,a.lngDefine7,a.lngDefine8,a.lngDefine9,a.lngDefine10,
a.dblDefin1,a.dblDefin2,a.dblDefin3,a.dblDefin4,a.dblDefin5,
a.dblDefin6,a.dblDefin7,a.dblDefin8,a.dblDefin9,a.dblDefin10,
a.strDefine1,a.strDefine2,a.strDefine3,a.strDefine4,a.strDefine5,
a.strDefine6,a.strDefine7,a.strDefine8,a.strDefine9,a.strDefine10,
b.strIdeaTypeName,
c.strUserCode_SignReport,c.strUserName_SignReport

FROM SignReport a

LEFT JOIN(SELECT lngIdeaTypeID, strIdeaTypeName FROM IdeaType)b
ON a.lngIdeaTypeID=b.lngIdeaTypeID

LEFT JOIN(SELECT lngUserID,
strUserCode AS strUserCode_SignReport,
strUserName AS strUserName_SignReport FROM Users)c
ON a.lngUserID=c.lngUserID)l
ON a.lngItemAcceptID=l.lngItemAcceptID '+@Where+@FieldOrder+N' SET @Rows=@@ROWCOUNT'

PRINT @SQL

EXEC SP_EXECUTESQL @SQL,N'@Rows INT OUTPUT',@Rows OUTPUT

=======================
处理结果：
消息 402，级别 16，状态 1，过程 e_SignReport，第 386 行
数据类型 nvarchar(max) 和 ntext 在 add 运算符中不兼容。
fyming 2009-06-19
  • 打赏
  • 举报
 回复
为了方便测试，把把整段代码放出来，请ai_li7758521帮忙看看，哪儿出了问题？


ALTER PROCEDURE [dbo].[e_SignReport]
@v INT,
@FieldShow NVARCHAR(MAX)=N'',
@FieldOrder NVARCHAR(MAX)=N'',
@Where NVARCHAR(MAX)=N'' ,

@Rows INT=NULL OUTPUT,
@lngErrCode INT=NULL OUTPUT,
@lngErrCode_1 INT=NULL OUTPUT
---------------------------------------------------------------

--WITH ENCRYPTION

AS

SET NOCOUNT ON

DECLARE
@SQL NVARCHAR(MAX),
@ID INT,
@isUsed BIT,
@dateActivityDate SMALLDATETIME,

@strPrefix NVARCHAR(30)

IF @v=5
BEGIN
SELECT @lngErrCode=0

--处理@FieldShow
IF @FieldShow=N'' SET @FieldShow=N'
a.lngItemAcceptID,a.lngBusinessTypeID,a.strItemAccepNo,
a.lngCustomerID,a.lngEmployeeID,a.bytType,a.bytState, a.lngUserID

b.strBusinessTypeName,
c.strCustomerCode,c.strCustomerName,c.strCustomerAlias,
d.strEmployeeCode,d.strEmployeeName,
e.strUserCode,e.strUserName,
f.lngItemAcceptDetailID,
f.dateActivityDate1,f.dateActivityDate2,f.dateActivityDate3,f.dateActivityDate4,f.dateActivityDate5,f.dateActivityDate6,
f.lngDefine1,f.lngDefine2,f.lngDefine3,f.lngDefine4,f.lngDefine5,f.lngDefine6,
f.dblDefin1,f.dblDefin2,f.dblDefin3,f.dblDefin4,f.dblDefin5,f.dblDefin6,
f.strDefine1,f.strDefine2,f.strDefine3,f.strDefine4,f.strDefine5,f.strDefine6,

g.lngItemAuditID,g.bytType_ItemAudit,g.strAuditIdea,g.lngUserID_ItemAudit,

h.lngItemRegisterID,h.lngGetReportNOID,
h.lngEmployeeID_ItemRegister,h.strEmpID,h.strEmpName,
h.dateActivityDate1_ItemRegister,
h.dateActivityDate2_ItemRegister,
h.dateActivityDate3_ItemRegister,
h.dateActivityDate4_ItemRegister,
h.dateActivityDate5_ItemRegister,
h.strRiskExplain,h.dblRiskRate,h.lngDays,h.lnghours,h.lngUserID_ItemRegister,
h.lngReportTypeID_GetReportNO,h.strReportNo,h.lngEmployeeID_GetReportNO,
h.strReportTypeName_GetReportNO,
h.strEmployeeCode_GetReportNO,h.strEmployeeName_GetReportNO,h.strEmployeeAlias_GetReportNO,
h.strEmployeeCode_ItemRegister,h.strEmployeeName_ItemRegister,h.strEmployeeAlias_ItemRegister,

i.lngItemCheckID_ItemCheck1,i.bytCheckType1,
i.dateActivityDate_ItemCheck1, i.strCheckIdea_ItemCheck1,
i.dateEditTime_ItemCheck1,
i.lngUserID_ItemCheck1,
i.strUserCode_ItemCheck1,i.strUserName_ItemCheck1,

j.lngItemCheckID_ItemCheck2,j.bytCheckType2,
j.dateActivityDate_ItemCheck2, j.strCheckIdea_ItemCheck2,
j.dateEditTime_ItemCheck2,
j.lngUserID_ItemCheck2,
j.strUserCode_ItemCheck2,j.strUserName_ItemCheck2,

k.lngItemCheckID_ItemCheck3,k.bytCheckType3,
k.dateActivityDate_ItemCheck3, k.strCheckIdea_ItemCheck3,
k.dateEditTime_ItemCheck3,
k.lngUserID_ItemCheck3,
k.strUserCode_ItemCheck3,k.strUserName_ItemCheck3,

l.lngSignReportID,l.lngItemAcceptID,l.lngIdeaTypeID,l.dateActivityDate,
l.strInvoiceNo,l.dblFactManHour1,l.dblFactManHour2,l.lngReportNum,
l.strDeliverID1,l.strDeliverName1,l.strDeliverID2,l.strDeliverName2,
l.strComment,l.dateEditTime,l.lngUserID,
l.lngDefine1,l.lngDefine2,l.lngDefine3,l.lngDefine4,l.lngDefine5,
l.lngDefine6,l.lngDefine7,l.lngDefine8,l.lngDefine9,l.lngDefine10,
l.dblDefin1,l.dblDefin2,l.dblDefin3,l.dblDefin4,l.dblDefin5,
l.dblDefin6,l.dblDefin7,l.dblDefin8,l.dblDefin9,l.dblDefin10,
l.strDefine1,l.strDefine2,l.strDefine3,l.strDefine4,l.strDefine5,
l.strDefine6,l.strDefine7,l.strDefine8,l.strDefine9,l.strDefine10,
l.strIdeaTypeName,
l.strUserCode_SignReport,l.strUserName_SignReport '

--处理@FieldOrder
IF @FieldOrder=N'' SET @FieldOrder=N' a.strItemAccepNo '
SET @FieldOrder=N' ORDER BY '+LTRIM(@FieldOrder)

--处理@Where
SET @Where=N' WHERE (1>0 ' +@Where+N')'
fyming 2009-06-19
  • 打赏
  • 举报
 回复
再经测试，还是发现@SQL1被强制认为是NTEXT了，而不是之前定义的NVARCHAR（MAX）。有人遇到过这样的情况吗？是不是运态SQL拼接字符串时会出现这样的问题？我用的是SQL2005。
ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 fyming 的回复:]
SET @SQL1='……一大堆字符……'+ @SQL2
[/Quote]Unicode常量使用N开头来指定：N'AUnicode strin'
fyming 2009-06-19
  • 打赏
  • 举报
 回复
发现把
SET @SQL1=N'……一大堆字符……'+ @SQL2
改成
SET @SQL1='……一大堆字符……'+ @SQL2 就不会报错

也就是说，去掉'号前的N。
兄弟们给解释一下好吗？在线等，谢谢！

ai_li7758521 2009-06-19
  • 打赏
  • 举报
 回复 
DECLARE 

@SQL1 NVARCHAR(MAX), 

@SQL2 NVARCHAR(MAX) 



SET @SQL2=N'WHERE 1>0 ' 



SET @SQL1=N'……一大堆字符……'+ @SQL2 



select [len]=len(@SQL1)



len

--------------------

917325



(1 行受影响)
加载更多回复（2）
nvarchar binary数据类型转换
nvarchar binary数据类型转换
MSSQL报错：参数数据类型 text 对于 replace 函数的参数 1 无效的解决办法
在sql中使用replace函数可以替换某个字段中的一些内容，但是如果字段是text类型那么使用replace函数就会报“参数数据类型text 对于replace 函数的参数1 无效。”，这个错误说明对text或ntext类型的数据在查询中不能进行字符串操作
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型
过程需要参数
亿级海量数据搜索，检索方案　HubbleDotNet
亿级海量数据搜索，检索方案 [IndexOnly] [Directory ('d:\test11\VEnglishNews\')] [DBTableName ('VEnglishNews')] [DBAdapter ('SQLSERVER2005')] [DBConnect ('Data Source=(local);Initial Catalog=Test;Integrated Security=True')] Create table VEnglishNews ( GroupId Int Untokenized NULL default 0 , GroupTitle NVarchar(max) Tokenized Analyzer 'EnglishAnalyzer' NULL default '' , SiteId Int Untokenized NULL default 0 , Site NVarchar(max) Tokenized Analyzer 'EnglishAnalyzer' NULL default '' , Title NVarchar(max) Tokenized Analyzer 'EnglishAnalyzer' NULL default '' , Content NVarchar(max) Tokenized Analyzer 'EnglishAnalyzer' NULL default '' , SearchContent NVarchar(max) Tokenized Analyzer 'EnglishAnalyzer' NULL default '' , Time DateTime Untokenized NULL default '1900-1-1' , Rank int Untokenized NULL default 1 , URL NVarchar(max) NULL , ImageUrl NVarchar(max) NULL ); 有时我们需要对某个文档指定权重，HubbleDotNet 中，我们通过在表中增加一个名为 Rank 的int 数据类型 Untokenized 索引类型的字段来指定文档的权重。 通过修改这个字段的值，我们可以控制该文档在查询时的权重。一个典型的例子，如果我们希望点击越多的文档，权重越高，那么我们只要在每次点击后，对相应文档的Rank 加n 就可以了。
java nvarchar max_sql server中使用nvarchar(MAX)代替ntext
两个字段目前来说存储的数据没什么大区别但是nvarchar(MAX)类型查询的时候方便一些，ntext查询的时候还得转换类型。比如：ntex类型：select * from 表 where convert(nvarchar(MAX),InfoContent) !=''nvarchar(MAX)类型：select * from 表 where InfoContent !=''NTEXT:默认情况下，...
MS-SQL Server

34,873

社区成员

254,640

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章