--这个是截取字符串的函数，在生成存储过程的时候调用了gocreate 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))asbegin 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, ProductOtherInfowhere --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, ',')) endGo
--删除存储过程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,SalesChannelValuewhere Product.ID=SalesChannelValue.ProductID and SalesChannel.ID=SalesChannelValue.SalesChannelID and Product.ID = @id and SalesChannelValue.Checked=1return @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+ ',' + @strfrom Product,ExtraInsureProductType,ExtraInsureProductTypeValuewhere Product.ID=ExtraInsureProductTypeValue.ProductID and ExtraInsureProductType.ID=ExtraInsureProductTypeValue.ExtraInsureProductTypeID and Product.ID = @id and ExtraInsureProductTypeValue.Checked=1return @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,CustomerRequirementValuewhere Product.ID=CustomerRequirementValue.ProductID and CustomerRequirement.ID=CustomerRequirementValue.CustomerRequirementID and Product.ID = @id and CustomerRequirementValue.Checked=1return @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+ ',' + @strfrom Product,TargetCustomer,TargetCustomerValuewhere Product.ID=TargetCustomerValue.ProductID and TargetCustomer.ID=TargetCustomerValue.TargetCustomerID and Product.ID = @id and TargetCustomerValue.Checked=1return @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,PaymentFrequencyValuewhere Product.ID=PaymentFrequencyValue.ProductID and PaymentFrequency.ID=PaymentFrequencyValue.PaymentFrequencyID and Product.ID = @id and PaymentFrequencyValue.Checked=1return @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,DurationValuewhere 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,DurationValuewhere 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,MarketTypeValuewhere Product.ID=MarketTypeValue.ProductID and MarketType.ID=MarketTypeValue.MarketTypeID and Product.ID = @id and MarketTypeValue.Checked=1return @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,PrimarySecondaryInsurewhere PrimarySecondaryInsure.ID=@id return @str end go
--删除函数drop function fun_SalesChanneldrop function fun_ExtraInsureProductTypedrop function fun_CustomerRequirementdrop function fun_TargetCustomerdrop function fun_PaymentFrequencydrop function fun_Duration_01drop function fun_Duration_02drop function fun_MarketTypedrop function fun_PrimarySecondaryInsure
