在将 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
b.state = 1 THEN ''审批通过'' WHEN OrderAuditStatus = 0 AND b.state = 1 AND b.OrderAuditDate IS NOT NULL
THEN ''未通过'' WHEN b.state = 2 THEN ''未通过'' END AS OrderAuditStatus,'+' b.OrderAuditDate'
+' from '+@From+' where '+@WhereStr+') t'+
' where rownum>(@PageIndex*@PageSize) and rownum<=((@PageIndex+1)*@PageSize)'
print @Sqlquery
exec sp_executesql @Sqlquery,N'@PageIndex int,@PageSize int',@PageIndex,@PageSize
END
exec up_order_OrderListForSearch 'eeee',1,1,1
麻烦大家帮忙看看这个存储过程哪写的不对啊?