--这个是截取字符串的函数，在生成存储过程的时候调用了
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