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)呀。如何解决?谢谢
@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)呀。如何解决?谢谢
解决方案 »
- 求一存储过程或sql语句,批量动态更新
- 高手帮忙看看这个存储过程!参数正确带进去,数据库不能插入...
- 求SQL语句,谢谢
- server2003操作系统 安装SQL2000 连接不上数据库!急急
- 对Sys.dm_exec_sql_text里statement_start_offset以及statement_end_offset 使用的不明白之处
- 一次插入若干条纪录,如果全部插入成功就写入数据表,如果发生任何错误就全部回滚,怎么写这样的Sql语句?
- 这样的SQL语句:select a1,a2 from a where purchase_date>'2001.01.01' 在ACCESS中怎么写?
- 排序的问题,请帮帮忙!
- 开发一数据库用binary存word文档和图片时它总是说“无法编辑该单元”,
- 怎么样得到一个表的第一条或第n条记录?
- ----大家帮帮忙-----
- sql Server2005windows身份验证登陆报错
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX) SET @SQL2=N'WHERE 1>0 ' SET @SQL1=N'……一大堆字符……'+ @SQL2 select [len]=len(@SQL1)len
--------------------
917325(1 行受影响)
SET @SQL1=N'……一大堆字符……'+ @SQL2
改成
SET @SQL1='……一大堆字符……'+ @SQL2 就不会报错也就是说,去掉'号前的N。
兄弟们给解释一下好吗?在线等,谢谢!
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 ENCRYPTIONASSET NOCOUNT ONDECLARE
@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')'
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 运算符中不兼容。
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 '
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
看看能不能跑?
真是怪了,同样的代码,为什么你可以而我就不行呢?2、TO 乳沟
即使注释掉最后一句也不行。语法分析通过,执行不了。
是不是和SQL的哪里的设置有关呢?
--結果如下 (@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,
.......................*/
刚才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?这和我的问题有点象,但即使是原贴,也好象没有一个最后的答案。
DECLARE
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX),
@SQL3 NVARCHAR(MAX)SET @SQL2=N'WHERE 1>0 '
SET @SQL1=N'……一大堆字符……'
SET @SQL3=@SQL1+@SQL2
那你这样试试。
即对@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多都没有问题,但做字符串拼接时,可能会出错。恳请高手指点一下!
varchar(max)+varchar(max)=》varchar(max)
varchar(max)+varchar(x)=》varchar(8000)
select @str1 = replicate( CAST('1' as nvarchar(max)),8006)
select len(@str1) -- 8000 --------------------
8006(1 行受影响)
在拼接字符串时,即:
--处理查询字符串
SET @SQL=N'SELECT '+@FieldShow+N' FROM ItemAccept a改为:SET @SQL=N'SELECT '+@FieldShow+CONVERT(NVARCHAR(MAX),N' FROM ItemAccept a即可!最后得出结论:对于NVARCHAR(MAX)数据类型的变量进行SET赋值时,如果字符超大,即使你在之前已将变量定义为MAX,但SQL还是会将它默认为NTEXT。因此,为了为避免出错,在赋值时加一道CONVERT(NVARCHAR(MAX),N' ……‘)即可。至于字符数究竟多大时会发生这种情况,我就不知道了。以本例测试为6K多,为什么不是4K?得问问高手了!贴子稍后结,谢谢ai_li7758521 和 乳沟。唉,这么多年了,乳沟还是叫乳沟。