SQL Server 2012中的存储过程nvarchar(max)长度错误 [英] Error in Stored Procedure nvarchar(max) length in SQL Server 2012
问题描述
我正在使用SP,以便将查询存储在字符串中,最后在字符串中对我需要用于SP的各种条件进行操作之后,我在SP中执行字符串。我在SQL Server 2012中使用nvarchar(max)进行查询。但是,一旦字符串超过限制,它就会显示语法不正确错误。如果我减少SP中的行,则此错误消失。我确信SP是正确的,因为当单独执行语句时,我得到了结果。在整个SP中,如果我删除SP中的空格,我会得到所需的结果。这个问题的原因是什么?有谁之前经历过这个吗 ?我完全被这个目瞪口呆。
这是SP
- 创建程序[dbo] 。[_ PTC_PayOff_Asset_List_Rpt]
- @CustomerAccountNumber NVARCHAR(MAX)= NULL
- ,@ LeaseSequenceNumber NVARCHAR(MAX)= NULL
- - ,@ PortfolioName NVARCHAR(MAX)= NULL
- ,@ QuoteStatus NVARCHAR(MAX)= NULL
- ,@ PayOffQuoteName NVARCHAR(MAX)= NULL
--AS
- 使用LW_Presidio_Dev
DECLARE @CustomerAccountNumber NVARCHAR(MAX);
DECLARE @LeaseSequenceNumber NVARCHAR(MAX);
DECLARE @PortfolioName NVARCHAR(MAX);
DECLARE @QuoteStatus NVARCHAR(MAX);
DECLARE @PayOffQuoteName NVARCHAR(MAX)
SET @CustomerAccountNumber ='300021';
SET @LeaseSequenceNumber ='41937_a2'; --253
SET @PortfolioName ='Presidio Technology Capital'; --Presidio Technology Capital
SET @QuoteStatus =''; - 打开
SET @PayOffQuoteName =''; --1792
IF @CustomerAccountNumber IS NULL BEGIN SET @CustomerAccountNumber =''END
IF @LeaseSequenceNumber IS NULL BEGIN SET @LeaseSequenceNumber = ''结束
IF @PortfolioName IS NULL BEGIN SET @PortfolioName =''END
IF @QuoteStatus IS NULL BEGIN SET @QuoteStatus =''END
IF @PayOffQuoteName IS NULL BEGIN SET @PayOffQuoteName =''END
DECLARE @SQLStatement NVARCHAR(MAX);
SET @SQLStatement ='
;有CTE_SalesRep
AS
(
选择
RS1.LeaseID
- ,(Security_UserProfile.FirstName)[SalesRep]
,(IsNull(Security_UserProfile.FirstName,''') ''+')+''''
+ IsNull(Security_UserProfile.MiddleName,''''+')+''''
+ IsNull(Security_UserProfile .LastName,'''''+'))[SalesRep]
FROM
(
SELECT
EmployeeAssignment_Entity.EntityID [LeaseID]
,MAX(EmployeeAssignment_Entity.EmployeeAssignmentID)[MaxEmployeeAssignmentID]
FROM
EmployeeAssignment_Entity
WHERE
EmployeeAssignment_Entity.EntityTypeID = 5 - 租赁
AND EmployeeAssignm ent_Entity.IsActive = 1 - 是
AND EmployeeAssignment_Entity.RoleFunctionID = 8 - 销售代表
GROUP BY
EmployeeAssignment_Entity.EntityID
)RS1
INNER JOIN EmployeeAssignment_Entity
ON EmployeeAssignment_Entity.EmployeeAssignmentID = RS1.MaxEmployeeAssignmentID
INNER JOIN Security_UserProfile
ON Security_UserProfile.UserID = EmployeeAssignment_Entity.UserID
),
CTE_AccountManager
AS
(
SELECT
RS1.LeaseID
- ,(Security_UserProfile.FirstName)[AccountManager]
,(IsNull(Security_UserProfile.FirstName,''''+')+''''
+ IsNull (Security_UserProfile.MiddleName,'''''+')+''''
+ IsNull(Security_UserProfile.LastName,'''''+'))[AccountManager]
FROM
(
SELECT
EmployeeAssignment_Entity.EntityID [LeaseID]
,MAX(EmployeeAssignment_Entity.EmployeeAssignmentID)[MaxEmployeeAssignmentID]
FROM
EmployeeAssignment_Entity
WHERE
EmployeeAssignment_Entity.EntityTypeID = 5 - 租赁
AND EmployeeAssignment_Entity.IsActive = 1 - 是
AND EmployeeAssignment_Entity.RoleFunctionID = 4 - 销售代表
GROUP BY
EmployeeAssignment_Entity.EntityID
)RS1
INNER JOIN EmployeeAssignment_Entity
ON EmployeeAssignment_Entity.EmployeeAssignmentID = RS1.MaxEmployeeAssignmentID
INNER JOIN Security_UserProfile
ON Security_UserProfile.UserID = EmployeeAssignment_Entity.UserID
),
CTE_PayoffReceivable AS
(
SELECT Receivable_Payoff.PayoffQuoteID,Receivable_Payoff.InventoryID
,CASE WHY Receivable_Payoff.ReceivableTypeID = 4 THEN SUM(Receivable_Payoff.Balance)ELSE 0.00 END As BasePayoffBalance
,CASE WHY Receivable_Payoff.ReceivableTypeID = 5 THEN SUM(Receivable_Payoff.Balance)ELSE 0.00 END作为BaseBuyoutBalance
,Receivable_Payoff.ReceivableTypeID
FROM Receivable_Payoff
--WHERE Receivable_Payoff.PayoffQuoteID = 4
GROUP BY Receivable_Payoff.PayoffQuoteID,Receivable_Payoff.InventoryID,Receivable_Payoff.ReceivableTypeID
),>
CTE_ReceivableGroup AS
(
SELECT
CTE_PayoffReceivable.InventoryID
,CTE_PayoffReceivable.PayoffQuoteID
,MAX(BasePayoffBalance)AS BasePayoffBalance
,MAX(BaseBuyoutBalance)AS BaseBuyoutBalance
来自CTE_PayoffReceivable
GROUP BY
CTE_PayoffReceivable.InventoryID
,CTE_PayoffReceivable.PayoffQuoteID
)
SELECT
GETDATE()[当前日期],
PayOff_Quote.QuoteName [支付报价名称],
PayOff_Quote.PayOffQuoteID [支付报价ID],
Customer_Profile.CustomerName [客户名称],
--Customer_Profile.CompanyName,
Customer_Profile.AccountNumber [账号],
Lease_profile .LeaseNumber [租约计划编号],
Customer_Profile.AccountNumber [账号],
Lease_Profile.SequenceNumber [租赁序列号],
CTE_AccountManager .AccountManager,
CTE_SalesRep.SalesRep,
支付off_FinancialDetail.ComputeDate [终止日期],
当PayOff_Quote.IsFullPayoff = 1时,'完整''ELSE''部分''结束为[终止报价类型],
PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName [支付报价状态],
Invoice_Number.InvoiceNumber [发票编号],
Payoff_QuoteInvoiceHeader.DueDate [发票到期日],
Payoff_QuoteInvoiceHeader.QuoteAmount [发票金额到期],
Inventory_FactorCategoryTemplate.FactorCategoryName [因子类别类型],
--Lease_Contract.MaturityDate,
Inventory_Profile .Manufacturer,
Inventory_Profile.Quantity,
Inventory_Profile.Description1 [设备说明],
Inventory_Profile.SerialNumber [序列号],>
Payoff_InventoryStatus_CnfgLocale.StatusName AS PayoffInventoryStatus,
--ISNULL(Payoff_FinancialDetail.BasePayO ffAmount,0.00)AS TotalBasePayoffAmount,
CTE_ReceivableGroup.BasePayoffBalance
,CTE_ReceivableGroup.BaseBuyoutBalance,
案例当Payoff_Quote.IsTerminationLetterExecuted = 1那么''是的''ELSE''不''结束,因为TerminationLetter已执行,
Inventory_LocationTemplate.LocationCode,
Inventory_Profile.OriginalCost
,Inventory_LocationTemplate.Address
,Inventory_LocationTemplate.Address2
,Inventory_LocationTemplate.City
,Template_State_CnfgLocale.StateShortName
,Inventory_LocationTemplate.ZIP
,ISNULL(Payoff_FinancialDetail.BaseBuyOutAmount,0.00)AS TotalBaseBuyOutAmount
,Inventory_Profile.InventoryID As AssetID
,Inventory_Profile.Alias
,Inventory_Profile .UnitNumber
来自PayOff_Quote
内部加入Lease_Profile O N
Lease_Profile.LeaseID = PayOff_Quote.LeaseID
内部加入Customer_Profile ON
Customer_Profile.CustomerID = Lease_Profile.CustomerID
INNER JOIN PayOff_QuoteStatus_CnfgLocale ON
PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusID = PayOff_Quote.QuoteStatusID
内部加入Payoff_InventoryInQuote ON
Payoff_InventoryInQuote.PayOffQuoteID = PayOff_Quote.PayOffQuoteID >
JOIN Payoff_InventoryStatus_CnfgLocale ON
Payoff_InventoryStatus_CnfgLocale.InventoryPayoffStatusID = Payoff_InventoryInQuote.InventoryPayoffStatusID
内部加入Inventory_Profile ON
Inventory_Profile.InventoryID = Payoff_InventoryInQuote.InventoryID
内部加入Inventory_FactorCategoryTemplate ON
Inventory_FactorCategoryTemplate.FactorCategoryID = Inventory_Profile.FactorCategory ID
内部加入Inventory_Location ON
Inventory_Location.InventoryID = Inventory_Profile.InventoryID
内部加入Inventory_LocationTemplate ON
Inventory_LocationTemplate。 LocationTemplateID = Inventory_Location.LocationTemplateID
JOIN Template_State_CnfgLocale ON
Template_State_CnfgLocale.StateID = Inventory_LocationTemplate.StateID
Inner Join Portfolio_Profile ON
Portfolio_Profile.PortfolioID = Lease_Profile.PortfolioID
JOIN Lease_Contract ON
PayOff_Quote.LeaseID = Lease_Contract.LeaseID
AND Lease_Contract.IsActive = 1
LEFT JOIN CTE_ReceivableGroup ON
CTE_ReceivableGroup.PayoffQuoteID = Payoff_Quote.PayOffQuoteID
AND Payoff_InventoryInQuote.InventoryID = CTE_ReceivableGroup.InventoryID
LEFT加入Payoff_FinancialDetail ON <
Payoff_FinancialDetail.PayOffQuoteID = PayOff_Quote.PayOffQuoteID
LEFT JOIN Payoff_QuoteInvoiceLeaseDetail ON
Payoff_QuoteInvoiceLeaseDetail.PayoffQuoteId = PayOff_Quote.PayoffQuoteId
LEFT加入Payoff_QuoteInvoiceHeader ON
Payoff_QuoteInvoiceHeader.PayOffQuoteInvoiceID = Payoff_QuoteInvoiceLeaseDetail.PayOffQuoteInvoiceID
LEFT JOIN Invoice_Number ON
Invoice_Number.InvoiceNumberId = Payoff_QuoteInvoiceHeader.InvoiceNumberId
LEFT JOIN CTE_SalesRep ON
CTE_SalesRep.LeaseID = Lease_Profile.LeaseID
LEFT JOIN CTE_AccountManager ON
CTE_AccountManager.LeaseID = Lease_Profile.LeaseID
- WHERE PayOff_Quote.PayoffQuoteId = 1
PRIMARY_FILTERCONDITIONS'
DECLARE @PrimaryFilterConditions NVARCHAR(MAX)
SET @PrimaryFilterConditions ='';
--IF LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
- BEGIN
- SET @PrimaryFilterConditions ='WHERE 1<> 1’ ;
- 结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)> 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Customer_Profile.AccountNumber = @CustomerAccountNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @ PayOffQuoteName AND
PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )> 0 AND LEN(@QuoteStatus)> 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)> 0和LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND PayOff_QuoteStatus_CnfgLocale。 PayOffQuoteStatusName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)> 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName
= @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)> 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @
QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@ LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)> 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @ PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Lease_Profile.SequenceNumber = @LeaseSequenceNumber';
结束
如果LEN(@ComeromerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)> 0和LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile。 PortfolioName = @PortfolioName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStat
usName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Portfolio_Profile.PortfolioName = @PortfolioName';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@ LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)> 0和LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)> 0 AND LEN(@ LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName';
END
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)> 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName';
END
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)> 0和LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName';
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )> 0 AND LEN(@QuoteStatus)> 0和LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Portfolio_Profile.PortfolioName = @PortfolioName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )> 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName';
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )< = 0 AND LEN(@QuoteStatus)> 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
结束
/ ** /
如果LEN(@CustomerAccountNumber)< = 0 AND LEN( @LeaseSequenceNumber)> 0 AND LEN(@PortfolioName)< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @ PrimaryFilterConditions ='WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber';
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )> 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Portfolio_Profile.PortfolioName = @PortfolioName ;
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )< = 0 AND LEN(@QuoteStatus)> 0和LEN(@PayOffQuoteName)< = 0
BEGIN
SET @PrimaryFilterConditions ='WHERE PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
结束
如果LEN(@CustomerAccountNumber)< = 0 AND LEN(@LeaseSequenceNumber)< = 0 AND LEN(@PortfolioName )< = 0 AND LEN(@QuoteStatus)< = 0 AND LEN(@PayOffQuoteName)> 0
BEGIN
SET @PrimaryFilterConditions ='WHERE Payoff_Quote.QuoteName = @PayOffQuoteName';
结束
如果LEN(@PrimaryFilterConditions)< = 0
BEGIN
SET @ PrimaryFilterConditions ='WHERE 1 = 1';
END
SET @SQLStatement = REPLACE(@ SQLStatement,'PRIMARY_FILTERCONDITIONS',@ PrimaryFilterConditions)
EXEC sp_executesql @ SQLStatement,N'@ CustomerAccountNumber NVARCHAR(MAX),@ LeaseSequenceNumber NVARCHAR(MAX),@ PortfolioName NVARCHAR(MAX),@ QuoteStatus NVARCHAR(MAX),@ PayOffQuoteName NVARCHAR(MAX)',@ CustomerAccountNumber,@ LeaseSequenceNumber,@ PortfolioName,@ QuoteStatus,@ PayOffQuoteName
I am using an SP such that i am storing the queries in a string and finally after performing manipulations in the string for the various conditions i need to use for the SP, I execute the string in the SP. I am using nvarchar(max) for the queries in SQL Server 2012. However, once the string crosses a limit, it shows "Incorrect Syntax" error . This error disappears if i reduce the lines in the SP. I am sure the SP is correct because when executing the statements separately, I got the result. In the whole SP too, if I remove the whitespaces in the SP, i get the results required. What is the cause of this issue ? Has anyone experienced this before ? I am completely dumbfounded by this.
This is the SP
--CREATE PROCEDURE [dbo].[_PTC_PayOff_Asset_List_Rpt]
-- @CustomerAccountNumber NVARCHAR(MAX) = NULL
-- ,@LeaseSequenceNumber NVARCHAR(MAX) = NULL
--, @PortfolioName NVARCHAR(MAX) = NULL
--,@QuoteStatus NVARCHAR(MAX) = NULL
--,@PayOffQuoteName NVARCHAR(MAX) = NULL
--AS
--use LW_Presidio_Dev
DECLARE @CustomerAccountNumber NVARCHAR(MAX);
DECLARE @LeaseSequenceNumber NVARCHAR(MAX);
DECLARE @PortfolioName NVARCHAR(MAX);
DECLARE @QuoteStatus NVARCHAR(MAX);
DECLARE @PayOffQuoteName NVARCHAR(MAX)
SET @CustomerAccountNumber = '300021';
SET @LeaseSequenceNumber = '41937_a2'; --253
SET @PortfolioName = 'Presidio Technology Capital'; --Presidio Technology Capital
SET @QuoteStatus = ''; --Open
SET @PayOffQuoteName = ''; --1792
IF @CustomerAccountNumber IS NULL BEGIN SET @CustomerAccountNumber = '' END
IF @LeaseSequenceNumber IS NULL BEGIN SET @LeaseSequenceNumber = '' END
IF @PortfolioName IS NULL BEGIN SET @PortfolioName = '' END
IF @QuoteStatus IS NULL BEGIN SET @QuoteStatus = '' END
IF @PayOffQuoteName IS NULL BEGIN SET @PayOffQuoteName = '' END
DECLARE @SQLStatement NVARCHAR(MAX);
SET @SQLStatement = '
;With CTE_SalesRep
AS
(
SELECT
RS1.LeaseID
--, (Security_UserProfile.FirstName) [SalesRep]
,(IsNull(Security_UserProfile.FirstName,'''''+') + ''''
+ IsNull(Security_UserProfile.MiddleName,'''''+') + ''''
+ IsNull(Security_UserProfile.LastName,'''''+')) [SalesRep]
FROM
(
SELECT
EmployeeAssignment_Entity.EntityID [LeaseID]
, MAX(EmployeeAssignment_Entity.EmployeeAssignmentID) [MaxEmployeeAssignmentID]
FROM
EmployeeAssignment_Entity
WHERE
EmployeeAssignment_Entity.EntityTypeID = 5 --Lease
AND EmployeeAssignment_Entity.IsActive = 1 --Yes
AND EmployeeAssignment_Entity.RoleFunctionID = 8 --Sales Representative
GROUP BY
EmployeeAssignment_Entity.EntityID
) RS1
INNER JOIN EmployeeAssignment_Entity
ON EmployeeAssignment_Entity.EmployeeAssignmentID = RS1.MaxEmployeeAssignmentID
INNER JOIN Security_UserProfile
ON Security_UserProfile.UserID = EmployeeAssignment_Entity.UserID
),
CTE_AccountManager
AS
(
SELECT
RS1.LeaseID
--, (Security_UserProfile.FirstName) [AccountManager]
,(IsNull(Security_UserProfile.FirstName,'''''+') + ''''
+ IsNull(Security_UserProfile.MiddleName,'''''+') + ''''
+ IsNull(Security_UserProfile.LastName,'''''+')) [AccountManager]
FROM
(
SELECT
EmployeeAssignment_Entity.EntityID [LeaseID]
, MAX(EmployeeAssignment_Entity.EmployeeAssignmentID) [MaxEmployeeAssignmentID]
FROM
EmployeeAssignment_Entity
WHERE
EmployeeAssignment_Entity.EntityTypeID = 5 --Lease
AND EmployeeAssignment_Entity.IsActive = 1 --Yes
AND EmployeeAssignment_Entity.RoleFunctionID = 4 --Sales Representative
GROUP BY
EmployeeAssignment_Entity.EntityID
) RS1
INNER JOIN EmployeeAssignment_Entity
ON EmployeeAssignment_Entity.EmployeeAssignmentID = RS1.MaxEmployeeAssignmentID
INNER JOIN Security_UserProfile
ON Security_UserProfile.UserID = EmployeeAssignment_Entity.UserID
),
CTE_PayoffReceivable AS
(
SELECT Receivable_Payoff.PayoffQuoteID,Receivable_Payoff.InventoryID
,CASE WHEN Receivable_Payoff.ReceivableTypeID = 4 THEN SUM(Receivable_Payoff.Balance) ELSE 0.00 END As BasePayoffBalance
,CASE WHEN Receivable_Payoff.ReceivableTypeID = 5 THEN SUM(Receivable_Payoff.Balance) ELSE 0.00 END As BaseBuyoutBalance
,Receivable_Payoff.ReceivableTypeID
FROM Receivable_Payoff
--WHERE Receivable_Payoff.PayoffQuoteID = 4
GROUP BY Receivable_Payoff.PayoffQuoteID,Receivable_Payoff.InventoryID,Receivable_Payoff.ReceivableTypeID
),
CTE_ReceivableGroup AS
(
SELECT
CTE_PayoffReceivable.InventoryID
,CTE_PayoffReceivable.PayoffQuoteID
,MAX(BasePayoffBalance) AS BasePayoffBalance
,MAX(BaseBuyoutBalance) AS BaseBuyoutBalance
FROM CTE_PayoffReceivable
GROUP BY
CTE_PayoffReceivable.InventoryID
,CTE_PayoffReceivable.PayoffQuoteID
)
SELECT
GETDATE() [Current Date],
PayOff_Quote.QuoteName [Payoff Quote Name],
PayOff_Quote.PayOffQuoteID [Payoff Quote ID],
Customer_Profile.CustomerName [Customer Name],
--Customer_Profile.CompanyName,
Customer_Profile.AccountNumber [Account Number],
Lease_profile.LeaseNumber [Lease Schedule Number],
Customer_Profile.AccountNumber [Account Number],
Lease_Profile.SequenceNumber [Lease Sequence Number],
CTE_AccountManager.AccountManager,
CTE_SalesRep.SalesRep,
Payoff_FinancialDetail.ComputeDate [Termination Date],
CASE WHEN PayOff_Quote.IsFullPayoff = 1 THEN ''Full'' ELSE ''Partial'' END AS [Termination Quote Type],
PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName [Payoff Quote Status],
Invoice_Number.InvoiceNumber [Invoice Number],
Payoff_QuoteInvoiceHeader.DueDate [Invoice Due Date],
Payoff_QuoteInvoiceHeader.QuoteAmount [Invoice Amount Due],
Inventory_FactorCategoryTemplate.FactorCategoryName [Factor Category Type],
--Lease_Contract.MaturityDate,
Inventory_Profile.Manufacturer,
Inventory_Profile.Quantity,
Inventory_Profile.Description1 [Equipment Description],
Inventory_Profile.SerialNumber [Serial Number],
Payoff_InventoryStatus_CnfgLocale.StatusName AS PayoffInventoryStatus,
--ISNULL(Payoff_FinancialDetail.BasePayOffAmount,0.00) AS TotalBasePayoffAmount,
CTE_ReceivableGroup.BasePayoffBalance
,CTE_ReceivableGroup.BaseBuyoutBalance,
CASE WHEN Payoff_Quote.IsTerminationLetterExecuted = 1 THEN ''Yes'' ELSE ''No'' END As TerminationLetterExecuted,
Inventory_LocationTemplate.LocationCode,
Inventory_Profile.OriginalCost
,Inventory_LocationTemplate.Address
,Inventory_LocationTemplate.Address2
,Inventory_LocationTemplate.City
,Template_State_CnfgLocale.StateShortName
,Inventory_LocationTemplate.ZIP
,ISNULL(Payoff_FinancialDetail.BaseBuyOutAmount,0.00) AS TotalBaseBuyOutAmount
,Inventory_Profile.InventoryID As AssetID
,Inventory_Profile.Alias
,Inventory_Profile.UnitNumber
FROM PayOff_Quote
Inner Join Lease_Profile ON
Lease_Profile.LeaseID = PayOff_Quote.LeaseID
Inner Join Customer_Profile ON
Customer_Profile.CustomerID = Lease_Profile.CustomerID
INNER JOIN PayOff_QuoteStatus_CnfgLocale ON
PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusID = PayOff_Quote.QuoteStatusID
Inner Join Payoff_InventoryInQuote ON
Payoff_InventoryInQuote.PayOffQuoteID = PayOff_Quote.PayOffQuoteID
JOIN Payoff_InventoryStatus_CnfgLocale ON
Payoff_InventoryStatus_CnfgLocale.InventoryPayoffStatusID = Payoff_InventoryInQuote.InventoryPayoffStatusID
Inner Join Inventory_Profile ON
Inventory_Profile.InventoryID = Payoff_InventoryInQuote.InventoryID
Inner Join Inventory_FactorCategoryTemplate ON
Inventory_FactorCategoryTemplate.FactorCategoryID = Inventory_Profile.FactorCategoryID
Inner Join Inventory_Location ON
Inventory_Location.InventoryID = Inventory_Profile.InventoryID
Inner Join Inventory_LocationTemplate ON
Inventory_LocationTemplate.LocationTemplateID = Inventory_Location.LocationTemplateID
JOIN Template_State_CnfgLocale ON
Template_State_CnfgLocale.StateID = Inventory_LocationTemplate.StateID
Inner Join Portfolio_Profile ON
Portfolio_Profile.PortfolioID = Lease_Profile.PortfolioID
JOIN Lease_Contract ON
PayOff_Quote.LeaseID = Lease_Contract.LeaseID
AND Lease_Contract.IsActive = 1
LEFT JOIN CTE_ReceivableGroup ON
CTE_ReceivableGroup.PayoffQuoteID = Payoff_Quote.PayOffQuoteID
AND Payoff_InventoryInQuote.InventoryID = CTE_ReceivableGroup.InventoryID
LEFT Join Payoff_FinancialDetail ON
Payoff_FinancialDetail.PayOffQuoteID = PayOff_Quote.PayOffQuoteID
LEFT JOIN Payoff_QuoteInvoiceLeaseDetail ON
Payoff_QuoteInvoiceLeaseDetail.PayoffQuoteId = PayOff_Quote.PayoffQuoteId
LEFT JOIN Payoff_QuoteInvoiceHeader ON
Payoff_QuoteInvoiceHeader.PayOffQuoteInvoiceID = Payoff_QuoteInvoiceLeaseDetail.PayOffQuoteInvoiceID
LEFT JOIN Invoice_Number ON
Invoice_Number.InvoiceNumberId = Payoff_QuoteInvoiceHeader.InvoiceNumberId
LEFT JOIN CTE_SalesRep ON
CTE_SalesRep.LeaseID = Lease_Profile.LeaseID
LEFT JOIN CTE_AccountManager ON
CTE_AccountManager.LeaseID = Lease_Profile.LeaseID
-- WHERE PayOff_Quote.PayoffQuoteId = 1
PRIMARY_FILTERCONDITIONS'
DECLARE @PrimaryFilterConditions NVARCHAR(MAX)
SET @PrimaryFilterConditions = '';
--IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <=0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
-- BEGIN
-- SET @PrimaryFilterConditions = 'WHERE 1 <> 1';
-- END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Customer_Profile.AccountNumber = @CustomerAccountNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName AND
PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName
= @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @
QuoteStatus';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Lease_Profile.SequenceNumber = @LeaseSequenceNumber';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStat
usName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Portfolio_Profile.PortfolioName = @PortfolioName';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) > 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Customer_Profile.AccountNumber = @CustomerAccountNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Portfolio_Profile.PortfolioName = @PortfolioName';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber AND Payoff_Quote.QuoteName = @PayOffQuoteName';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Portfolio_Profile.PortfolioName = @PortfolioName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Portfolio_Profile.PortfolioName = @PortfolioName AND Payoff_Quote.QuoteName = @PayOffQuoteName';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Payoff_Quote.QuoteName = @PayOffQuoteName AND PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
/**/
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) > 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Lease_Profile.SequenceNumber = @LeaseSequenceNumber';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) > 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Portfolio_Profile.PortfolioName = @PortfolioName';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) > 0 AND LEN(@PayOffQuoteName) <= 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE PayOff_QuoteStatus_CnfgLocale.PayOffQuoteStatusName = @QuoteStatus';
END
IF LEN(@CustomerAccountNumber) <= 0 AND LEN(@LeaseSequenceNumber) <= 0 AND LEN(@PortfolioName) <= 0 AND LEN(@QuoteStatus) <= 0 AND LEN(@PayOffQuoteName) > 0
BEGIN
SET @PrimaryFilterConditions = 'WHERE Payoff_Quote.QuoteName = @PayOffQuoteName';
END
IF LEN(@PrimaryFilterConditions)<=0
BEGIN
SET @PrimaryFilterConditions='WHERE 1=1';
END
SET @SQLStatement = REPLACE(@SQLStatement, 'PRIMARY_FILTERCONDITIONS', @PrimaryFilterConditions)
EXEC sp_executesql @SQLStatement,N'@CustomerAccountNumber NVARCHAR(MAX),@LeaseSequenceNumber NVARCHAR(MAX),@PortfolioName NVARCHAR(MAX),@QuoteStatus NVARCHAR(MAX),@PayOffQuoteName NVARCHAR(MAX)',@CustomerAccountNumber,@LeaseSequenceNumber,@PortfolioName,@QuoteStatus,@PayOffQuoteName
推荐答案
实际上在Sp的动态查询中由于语法错误或单引号('),会出现此问题。尝试检查一下。如果你遇到这个问题仍然存在。请提供您的查询/ SP;
Actually in dynamic query in Sp this issue occurs due to the syntax error or single quotes ("'"). Try checking for this. Still if you face the issue. Please provide your query/SP;
这篇关于SQL Server 2012中的存储过程nvarchar(max)长度错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!