預存程序接收多個值(用分隔字元號的字串參數)的用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