USE [PLA_SCP]
GO
/****** Object: StoredProcedure [dbo].[P_GetStoreInfo_ByStoreCodes] Script Date: 04/01/2015 09:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Hao.yuan>
-- Create date: <2015-3-13>
-- Description: <根据门店编码集合获取相应门店信息>
-- =============================================
ALTER proc [dbo].[P_GetStoreInfo_ByStoreCodes]
(
@storeCodeList varchar(100)
)
--set @storecodelist='000001,000002'
as
begin
select SeparatorValue as StoreCode into #tmp from dbo.STRING_SPLIT(@storecodelist,',') --#tmp 临时表,记得最后要drop掉
select a.StoreCode,a.StoreName,a.StoreTypeCode,b.StoreTypeName,b.BrandName,c.Dictionary_Name as Region,
d.Dictionary_Name as Province,e.Dictionary_Name as City from dbo.Store_Info a
left join dbo.Store_Type b on a.StoreTypeCode=b.StoreTypeCode
left join dbo.Sys_DictionaryItem c on a.StoreAddressRegion=c.Dictionary_Code and c.Category_ID='Area'
left join dbo.Sys_DictionaryItem d on a.StoreAddressProvince=d.Dictionary_Code and d.Category_ID='Province'
left join dbo.Sys_DictionaryItem e on a.StoreAddressCity=e.Dictionary_Code and e.Category_ID='City'
inner join #tmp f on a.StoreCode=f.StoreCode
drop table #tmp
end
传进来的参数格式为:001,002,003...
这里用到了一个表值函数:dbo.STRING_SPLIT
CREATE function [dbo].[STRING_SPLIT](@Str VARCHAR(max),@Separator VARCHAR(10))
RETURNS @TEMP TABLE(SeparatorValue VARCHAR(100))
AS
BEGIN
DECLARE @I INT
SET @Str=RTRIM(LTRIM(@Str))
SET @I=CHARINDEX(@Separator,@Str)
WHILE @I>=1
BEGIN
INSERT @TEMP VALUES(LEFT(@Str,@I-1))
SET @Str=SUBSTRING(@Str,@I+1,LEN(@Str)-@I)
SET @I=CHARINDEX(@Separator,@Str)
END
IF @Str<>'\'
INSERT @TEMP VALUES(@Str)
RETURN
END