日期:2014-05-18  浏览次数:20920 次

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