在将 varchar 值 ' select ' 转换成数据类型 int 时失败。解决思路
在将 varchar 值 ' select ' 转换成数据类型 int 时失败。
USE [foundercrm]
GO
/****** Object: StoredProcedure [dbo].[up_order_OrderListForSearch] Script Date: 06/21/2012 09:07:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[up_order_OrderListForSearch]
@WhereStr varchar(500),
@PageIndex int,
@PageSize int,
@RowCount int out
AS
BEGIN
declare @Sqlquery nvarchar(2000)
declare @From nvarchar(2000)
set @From='BOBasicInfo AS a INNER JOIN
View_Order AS b ON a.BOID = b.BOID INNER JOIN
BOCustomerInfo AS d ON a.BOID = d.BOID INNER JOIN
SalesType AS e ON a.salesType = e.SalesTypeID LEFT OUTER JOIN
BOAgents AS i ON b.AgentID = i.AgentID AND a.BOID = i.BOID INNER JOIN
ProductGroup AS g ON a.ProductGroupCode = g.ProductGroupCode INNER JOIN
CBInfo AS k ON a.CustomerID = k.CID INNER JOIN
Industry AS f ON k.IndustryID = f.IndustryID INNER JOIN
View_OrderSumPrice AS j ON b.OrderId = j.OrderId INNER JOIN
CAInfocache AS s ON k.CDBFormalID = s.Cus_ID INNER JOIN
ManageRegion AS r ON d.ManageAreaID = r.ManageRegionID INNER JOIN
CDB_A_Region AS q ON r.FatherId = q.ID LEFT OUTER JOIN
StockBatchBO AS xx ON a.BOID = xx.BOID LEFT OUTER JOIN
StockBatch AS xxx ON xx.StockBatchID = xxx.StockBatchID LEFT OUTER JOIN
Employee AS ex ON k.TradeManagerID = ex.UserCode INNER JOIN
Employee AS em1 ON k.ISID = em1.UserCode INNER JOIN
Employee AS em2 ON k.OSID = em2.UserCode INNER JOIN
Employee AS em3 ON k.TradeManagerID = em3.UserCode'
set @Sqlquery=' select ' + @RowCount+'=count(*) from '+@From+' where '+@WhereStr
exec sp_executesql @Sqlquery,N'@RowCount int output',@RowCount output
set @Sqlquery='select * from ('+
'select a.BOEPCode,
a.ProjectName,
a.CustomerName,
k.OSID AS OSCode,
k.ISID AS ISCOde,
k.TradeManagerID,
e.SalesTypeName,
CASE a.SalesType WHEN 1 THEN '' WHEN 2 THEN i.AgentName ELSE '' END AS PartnerName,'+' a.BOMidSPFullName,
CASE a.SalesType WHEN 1 THEN (CASE WHEN BoMidSPFullName <> '' THEN BOMidSPFullName ELSE b.CustomerName END)
ELSE i.AgentName END AS SoldToPartyName, g.Name, CASE b.State when 0 then ''未做''
when 1 then ''下单''
when 2 then ''撤消'' END AS OrderState, '+
'i.AgentName, a.ProductGroupCode, a.salesType, b.SOCode, b.POCode, f.IndustryName,
CASE b.OrderType WHEN 0 THEN ''标配订单'' ELSE ''特配订单'' END AS OrderTypeName,'+' b.OperatDate, a.BOCode, k.OSName, k.ISName, k.TradeManagerName,
a.CustomerID, b.OrderId, a.ProjectName AS Expr1, b.AgentID AS VAPCode, q.Region AS RegionName, r.RegionName AS ManageRegionName, '' AS Rad_1,
''KAB'' AS AttributeName, d.ManageAreaID AS ManageRegionID, d.IndustryID, b.OrderCreateDate, b.OrderCode, b.OrderType, b.ShipToPartyID, b.SendInvoiceInfoID,
j.sumPrice, xx.StockBatchID, ex.UserID AS TradeManagerCode, em1.UserID AS ISEName, em2.UserID AS OSEName, em3.UserID AS TradeManagerEName,
xxx.StockBatchName, xxx.StockBatchStatus, CASE WHEN b.OrderAuditDate IS NULL THEN ''未审批'' WHEN OrderAuditStatus = 1 AND
USE [foundercrm]
GO
/****** Object: StoredProcedure [dbo].[up_order_OrderListForSearch] Script Date: 06/21/2012 09:07:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[up_order_OrderListForSearch]
@WhereStr varchar(500),
@PageIndex int,
@PageSize int,
@RowCount int out
AS
BEGIN
declare @Sqlquery nvarchar(2000)
declare @From nvarchar(2000)
set @From='BOBasicInfo AS a INNER JOIN
View_Order AS b ON a.BOID = b.BOID INNER JOIN
BOCustomerInfo AS d ON a.BOID = d.BOID INNER JOIN
SalesType AS e ON a.salesType = e.SalesTypeID LEFT OUTER JOIN
BOAgents AS i ON b.AgentID = i.AgentID AND a.BOID = i.BOID INNER JOIN
ProductGroup AS g ON a.ProductGroupCode = g.ProductGroupCode INNER JOIN
CBInfo AS k ON a.CustomerID = k.CID INNER JOIN
Industry AS f ON k.IndustryID = f.IndustryID INNER JOIN
View_OrderSumPrice AS j ON b.OrderId = j.OrderId INNER JOIN
CAInfocache AS s ON k.CDBFormalID = s.Cus_ID INNER JOIN
ManageRegion AS r ON d.ManageAreaID = r.ManageRegionID INNER JOIN
CDB_A_Region AS q ON r.FatherId = q.ID LEFT OUTER JOIN
StockBatchBO AS xx ON a.BOID = xx.BOID LEFT OUTER JOIN
StockBatch AS xxx ON xx.StockBatchID = xxx.StockBatchID LEFT OUTER JOIN
Employee AS ex ON k.TradeManagerID = ex.UserCode INNER JOIN
Employee AS em1 ON k.ISID = em1.UserCode INNER JOIN
Employee AS em2 ON k.OSID = em2.UserCode INNER JOIN
Employee AS em3 ON k.TradeManagerID = em3.UserCode'
set @Sqlquery=' select ' + @RowCount+'=count(*) from '+@From+' where '+@WhereStr
exec sp_executesql @Sqlquery,N'@RowCount int output',@RowCount output
set @Sqlquery='select * from ('+
'select a.BOEPCode,
a.ProjectName,
a.CustomerName,
k.OSID AS OSCode,
k.ISID AS ISCOde,
k.TradeManagerID,
e.SalesTypeName,
CASE a.SalesType WHEN 1 THEN '' WHEN 2 THEN i.AgentName ELSE '' END AS PartnerName,'+' a.BOMidSPFullName,
CASE a.SalesType WHEN 1 THEN (CASE WHEN BoMidSPFullName <> '' THEN BOMidSPFullName ELSE b.CustomerName END)
ELSE i.AgentName END AS SoldToPartyName, g.Name, CASE b.State when 0 then ''未做''
when 1 then ''下单''
when 2 then ''撤消'' END AS OrderState, '+
'i.AgentName, a.ProductGroupCode, a.salesType, b.SOCode, b.POCode, f.IndustryName,
CASE b.OrderType WHEN 0 THEN ''标配订单'' ELSE ''特配订单'' END AS OrderTypeName,'+' b.OperatDate, a.BOCode, k.OSName, k.ISName, k.TradeManagerName,
a.CustomerID, b.OrderId, a.ProjectName AS Expr1, b.AgentID AS VAPCode, q.Region AS RegionName, r.RegionName AS ManageRegionName, '' AS Rad_1,
''KAB'' AS AttributeName, d.ManageAreaID AS ManageRegionID, d.IndustryID, b.OrderCreateDate, b.OrderCode, b.OrderType, b.ShipToPartyID, b.SendInvoiceInfoID,
j.sumPrice, xx.StockBatchID, ex.UserID AS TradeManagerCode, em1.UserID AS ISEName, em2.UserID AS OSEName, em3.UserID AS TradeManagerEName,
xxx.StockBatchName, xxx.StockBatchStatus, CASE WHEN b.OrderAuditDate IS NULL THEN ''未审批'' WHEN OrderAuditStatus = 1 AND