在将 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)
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)