存储过程接收多个值(用分隔符号的字符串参数)的用in查询

--这个是截取字符串的函数，在生成存储过程的时候调用了
go
create function [dbo].[f_split](@SourceSql varchar(max),@StrSeprate varchar(10))
    returns @temp table(Rowvalue varchar(1000))
as
    begin
        declare @i int
        set @SourceSql=rtrim(ltrim(@SourceSql))
        set @i=charindex(@StrSeprate,@SourceSql)
        while @i>=1
        begin
            insert @temp values(left(@SourceSql,@i-1))
            set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
            set @i=charindex(@StrSeprate,@SourceSql)
        end
        if @SourceSql<>''
        insert @temp values(@SourceSql)
     return
    end
  go

 

 

调用例子：

--创建查询的存储过程，参数为Product_ID

create procedure sel_table(@Sel_ProductID varchar(1000))
as
begin
 select  distinct Product.Name as '产品名称',
       InsureCompany.Name as '寿险公司',
       dbo.fun_SalesChannel(Product.ID) as '销售渠道',
       ProductType.Name as '产品类型',
       dbo.fun_ExtraInsureProductType(Product.ID) as '附加险产品类型',
       dbo.fun_PrimarySecondaryInsure(Product.PrimarySecondaryInsureID) as '主附险/计划',
       dbo.fun_CustomerRequirement(Product.ID) as '客户需求',
       dbo.fun_TargetCustomer(Product.ID) as '目标客户',
       isnull(CONVERT(varchar(50),InsureAge.insureFrom),'')+
          InsureAge.insurefromUnit+isnull(CONVERT(varchar(50),InsureAge.insureTo),'')+
          InsureAge.InsureToUnit as '投保年龄',
       dbo.fun_Duration_01(Product.ID) as '保险期间',
       dbo.fun_PaymentFrequency(Product.ID) as '交费频率',
       dbo.fun_Duration_02(Product.ID) as '交费期间',
       dbo.fun_MarketType(Product.ID) as '市场细分',
       (case ProductRole.checkedNew
            when '1' then '新产品'+convert(varchar(50),ProductRole.newDate,20)
            when '0' then '' end) +
       (case ProductRole.CheckedTop3
         when '1' then '销售前三名' +CONVERT(varchar(50),ProductRole.Top3StartDate,20)+ '至'+
         isnull((CONVERT(varchar(50),ProductRole.Top3EndDate,20)),'')
         when '0' then ''  end) as '产品角色',
        isnull(CONVERT(varchar(50),Product.SalesStartDate,20),'')+
           '至' +isnull(CONVERT(varchar(50),Product.SalesEndDate,20),'')  as '销售时间',
       ProductOtherInfo.SellingPoint as'销售卖点',
       ProductOtherInfo.PrimaryInsureResponsibility as '主要保险责任',
       ProductOtherInfo.OtherCharacteristic as '其它特点',
       ProductOtherInfo.CheckInvestInsureRule as '核保/投保规则',
       ProductOtherInfo.Fee as '费用',
       ProductOtherInfo.SalesSituation as '销售情况',
       ProductOtherInfo.Advantage as '优点',
       ProductOtherInfo.Shortcoming as '缺点',
       ProductOtherInfo.Memo as '备注',
       ProductOtherInfo.AttachMent as'附件'
from Product,
     InsureCompany,
     ProductType,
     PrimarySecondaryInsure,
     InsureAge,
     ProductRole,
     ProductOtherInfo
where --charindex( ', '   + CONVERT(varchar(100), Product.ID)   +   ', ', ', '   +  @Sel_ProductID    +   ', ')   >   0 and
   Product.InsureCompanyID=InsureCompany.ID and
      Product.ProductTypeID=ProductType.ID and
      Product.InsureAgeID=InsureAge.ID and
      Product.ProductRoleID=ProductRole.ID and
      Product.ProductOtherInfoID=ProductOtherInfo.ID
      --and Product.ID in ('B9789F3B-8A26-4803-9676-0C19C911452A','52D35A3F-5B85-40ED-BA1E-B96770CEC6FE')
      and Product.ID in(select Rowvalue from dbo.f_split(@Sel_ProductID, ','))    
end
Go

--删除存储过程
drop procedure sel_table

select * from Product where Product.ID in ('B9789F3B-8A26-4803-9676-0C19C911452A','52D35A3F-5B85-40ED-BA1E-B96770CEC6FE')

--执行存储过程
exec sel_table 'B9789F3B-8A26-4803-9676-0C19C911452A,52D35A3F-5B85-40ED-BA1E-B96770CEC6FE'

 

--页面上使用到的函数

--创建函数，读取多选项

--销售渠道函数
create function [dbo].[fun_SalesChannel](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = SalesChannel.Name + ',' + @str 
from Product,SalesChannel,SalesChannelValue
where Product.ID=SalesChannelValue.ProductID and
      SalesChannel.ID=SalesChannelValue.SalesChannelID and
      Product.ID = @id and
      SalesChannelValue.Checked=1
return @str
end
go

--附加险产品类型函数
create function [dbo].[fun_ExtraInsureProductType](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = ExtraInsureProductType.Name+ ',' + @str
from Product,ExtraInsureProductType,ExtraInsureProductTypeValue
where Product.ID=ExtraInsureProductTypeValue.ProductID and
      ExtraInsureProductType.ID=ExtraInsureProductTypeValue.ExtraInsureProductTypeID and
      Product.ID = @id and
      ExtraInsureProductTypeValue.Checked=1
return @str
end
go

--客户需求
create function [dbo].[fun_CustomerRequirement](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = CustomerRequirement.Name+ ',' +@str
from Product,CustomerRequirement,CustomerRequirementValue
where Product.ID=CustomerRequirementValue.ProductID and
      CustomerRequirement.ID=CustomerRequirementValue.CustomerRequirementID and
      Product.ID = @id and
      CustomerRequirementValue.Checked=1
return @str
end
go

--目标客户函数
create function [dbo].[fun_TargetCustomer](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = TargetCustomer.Name+ ',' + @str
from Product,TargetCustomer,TargetCustomerValue
where Product.ID=TargetCustomerValue.ProductID and
      TargetCustomer.ID=TargetCustomerValue.TargetCustomerID and
      Product.ID = @id and
      TargetCustomerValue.Checked=1
return @str
end
go

--交费频率
create function [dbo].[fun_PaymentFrequency](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = PaymentFrequency.Name + ',' + @str 
from Product,PaymentFrequency,PaymentFrequencyValue
where Product.ID=PaymentFrequencyValue.ProductID and
      PaymentFrequency.ID=PaymentFrequencyValue.PaymentFrequencyID and
      Product.ID = @id and
      PaymentFrequencyValue.Checked=1
return @str
end
go

--保险期间函数
create function [dbo].[fun_Duration_01](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = Duration.DurationName+DurationValue.Value+','+ @str
from Product,Duration,DurationValue
where Product.ID=DurationValue.ProductID and
      Duration.ID=DurationValue.DurationID and
      Product.ID = @id and
      Duration.DurationTypeItem='保险期间' and
      DurationValue.Checked=1
return @str
end
go

--交费期间函数
create function [dbo].[fun_Duration_02](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = Duration.DurationName+DurationValue.Value+','+ @str
from Product,Duration,DurationValue
where Product.ID=DurationValue.ProductID and
      Duration.ID=DurationValue.DurationID and
      Product.ID = @id and
      Duration.DurationTypeItem='交费期间' and
      DurationValue.Checked=1
return @str
end
go

--市场细分函数
create function [dbo].[fun_MarketType](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = MarketType.Name + ',' + @str 
from Product,MarketType,MarketTypeValue
where Product.ID=MarketTypeValue.ProductID and
      MarketType.ID=MarketTypeValue.MarketTypeID and
      Product.ID = @id and
      MarketTypeValue.Checked=1
return @str
end
go

---主附险计划函数
create function [dbo].[fun_PrimarySecondaryInsure](@id uniqueidentifier) returns nvarchar(1000)
as
begin
declare @str nvarchar(1000)
set @str = ''
select @str = PrimarySecondaryInsure.Name
from Product,PrimarySecondaryInsure
where PrimarySecondaryInsure.ID=@id
return @str
end
go

--删除函数
drop function fun_SalesChannel
drop function fun_ExtraInsureProductType
drop function fun_CustomerRequirement
drop function fun_TargetCustomer
drop function fun_PaymentFrequency
drop function fun_Duration_01
drop function fun_Duration_02
drop function fun_MarketType
drop function fun_PrimarySecondaryInsure

 

转载于:https://www.cnblogs.com/xffy1028/archive/2011/03/02/2344335.html