根据Code集捞数据

来源：互联网发布：网络奇葩十大奇葩食物编辑：程序博客网时间：2024/04/29 21:55

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

0 0