正常的传递 @SendStationID='''1'',''2''' 是无效，改用 @SendStationID='1,2,3,003,002' 调用以下的存储过程可以实现in 查询效果
USE [ztwl]
GO
/****** Object: StoredProcedure [dbo].[SelectPage_StationTransferFee_Second] Script Date: 04/11/2019 14:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectPage_StationTransferFee_Second]
@begincount varchar(50), --开始条数
@PageSize varchar(50) , --每页多少条记录
@Where nvarchar(4000) =N'' , --条件语句
@StartStationID varchar(50), --始发分拨网点
@SendStationID varchar(4000), --发站网点
@InStationID varchar(50) --入库网点
as
BEGIN
--------begin--组合发站参数临时表-----begin-----
DECLARE @Temp_Array varchar(max) set @Temp_Array=@SendStationID declare @Temp_Variable varchar(max) create table #Temp_SendTable(Item varchar(max)) ---定义发站ID临时表 while(LEN(@Temp_Array) > 0) begin if(CHARINDEX(',',@Temp_Array) = 0) begin set @Temp_Variable = @Temp_Array set @Temp_Array = '' end else begin set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1) set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1) end insert into #Temp_SendTable(Item) values(@Temp_Variable) end
------------组合发站参数------end--------------------
--- 第一部分：区域网点中转费
SELECT * INTO #Bas_FN_Area_StationConfig_Temp FROM (SELECT * from Bas_FN_Area_StationConfig_Daily WHERE 1=2 ) as x
--1.1用始发分拨+发站网点+入库网点查询区域网点中转费报表；
IF LEN(@StartStationID)>0
BEGIN
Insert into #Bas_FN_Area_StationConfig_Temp
SELECT * from Bas_FN_Area_StationConfig_Daily WHERE UpBelongCenterID= @StartStationID
and exists(select 1 from #Temp_SendTable(nolock) where #Temp_SendTable.Item=StartStationID) and InStationID=@InStationID and IfDel=0
-- PRINT '有--始发分拨查询'
end
else
BEGIN
Insert into #Bas_FN_Area_StationConfig_Temp
SELECT * from Bas_FN_Area_StationConfig_Daily WHERE exists(select 1 from #Temp_SendTable(nolock) where #Temp_SendTable.Item=StartStationID) and InStationID=@InStationID and IfDel=0
-- PRINT '无--始发分拨查询'
end
--1.2创建已有的+产品列表临时表
SELECT * INTO #cityList_Temp FROM (SELECT a.FinalPlaceCityID , a.FinalPlaceCity,a.ProductID from #Bas_FN_Area_StationConfig_Temp a ) AS y
-- SELECT * from #Bas_FN_Area_StationConfig_Temp
--- 第二部分：网点中转费
--1.2发站网点+入库网点询+排除网点中转费重复的城市和产品
SELECT * INTO #Bas_FN_StationConfig_Temp FROM (
SELECT * from Bas_FN_StationConfig a WHERE exists(select 1 from #Temp_SendTable(nolock) where #Temp_SendTable.Item=a.UpStationID) and a.StartStationID=@InStationID and a.IfDel=0
and NOT EXISTS (SELECT b.FinalPlaceCityID from #cityList_Temp b WHERE a.FinalPlaceCityID=b.FinalPlaceCityID and a.ProductID=b.productID )
) as xx
-- select count(*) from #cityList_Temp
--1.3 合并区域和网点费用 UpBelongCenterID,UpBelongCenter,QuoteBelongCenterID,QuoteBelongCenter,
SELECT * INTO #Bas_FN_StationConfig_Finally_Temp FROM
(
SELECT AreaCode,AreaName,StartStation as UpStation ,StartStationID as UpStationID ,InStation as StartStation ,InStationID as StartStationID,FinalPlaceCity,FinalPlaceCityID,FinalPlaceProvince,FinalPlaceProvinceID ,Product,ProductID,
InOutType,Limit,StartWeightOne,EndWeightOne,WeightPriceOne,VolumnPriceOne,StartWeightTwo,EndWeightTwo,WeightPriceTwo,VolumnPriceTwo,StartWeightThree,EndWeightThree,WeightPriceThree,VolumnPriceThree,StartWeightFour,EndWeightFour,WeightPriceFour,VolumnPriceFour,StartWeightFive,EndWeightFive,WeightPriceFive,VolumnPriceFive,StartWeightSix,EndWeightSix,WeightPriceSix,VolumnPriceSix,
UpBelongCenterID, UpBelongCenter, QuoteBelongCenterID, QuoteBelongCenter
from #Bas_FN_Area_StationConfig_Temp
UNION ALL
SELECT '' AS AreaCode,'' as AreaName, UpStation,UpStationID,StartStation,StartStationID,FinalPlaceCity,FinalPlaceCityID,FinalPlaceProvince,FinalPlaceProvinceID ,Product,ProductID,
CASE Status WHEN 0 THEN '出港网点中转费' ELSE '进港网点中转费' END as InOutType,Limit,StartWeightOne,EndWeightOne,WeightPriceOne,VolumnPriceOne,StartWeightTwo,EndWeightTwo,WeightPriceTwo,VolumnPriceTwo,StartWeightThree,EndWeightThree,WeightPriceThree,VolumnPriceThree,StartWeightFour,EndWeightFour,WeightPriceFour,VolumnPriceFour,StartWeightFive,EndWeightFive,WeightPriceFive,VolumnPriceFive,StartWeightSix,EndWeightSix,WeightPriceSix,VolumnPriceSix,b.BelongCenterID as UpBelongCenterID,b.BelongCenter as UpBelongCenter,c.BelongCenterID as QuoteBelongCenterID,c.BelongCenter as QuoteBelongCenter
FROM
(SELECT a.*,
(SELECT top 1 aa.EndStationID from Bas_FinalPlace aa WHERE aa.ifDelID=0 AND aa.QuoteCityID=a.FinalPlaceCityID) as EndStationID
from #Bas_FN_StationConfig_Temp a ) as aaa
LEFT JOIN Bas_StationInformation b on aaa.UpStationID=b.StationID and b.IfDel=0
LEFT JOIN Bas_StationInformation c on aaa.EndStationID=c.StationID and c.IfDel=0
) AS xyz
--分页
DECLARE @sql nvarchar(4000)
set @sql=' select * from ( SELECT top '+@PageSize+' ROW_NUMBER() Over(order by AreaCode desc ) as rownumid, a.* from #Bas_FN_StationConfig_Finally_Temp a
'+@where+' ) as pagetable where rownumid>'+@begincount
PRINT @sql
EXEC(@sql)
--总数
set @sql=' SELECT COUNT(*) from #Bas_FN_StationConfig_Finally_Temp '+@where+''
EXEC(@sql)
DROP TABLE #Bas_FN_Area_StationConfig_Temp
DROP TABLE #cityList_Temp
DROP TABLE #Bas_FN_StationConfig_Temp
DROP TABLE #Bas_FN_StationConfig_Finally_Temp
DROP TABLE #Temp_SendTable
END