在将 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
麻烦大家帮忙看看这个存储过程哪写的不对啊？
[解决办法]
自己检查那个转换字段的值，估计是有不能够被转换为int的值
[解决办法]
set @Sqlquery=' select ' + @RowCount+'=count(*) from '+@From+' where '+@WhereStr
这一句
试改为
set @Sqlquery=' select '' + @RowCount+''=count(*) from '+@From+' where '+@WhereStr
[解决办法]
那把前后两个''去掉呢?
要不就得转换成字符串
[解决办法]
exec up_order_OrderListForSearch 'eeee',1,1,1
这个显然有问题，第一个参数是where条件的字符串拼接，你怎么就传递了“eeee”，那算什么意思？
[解决办法]
如果只是测试下效果，改为：exec up_order_OrderListForSearch '1=1',1,1,1
