SQL_Server_2012存储过程及调用

SQL-Server2012存储过程、函数及调用 黑名单维护 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[add_value_to_black_user_list]    Script Date: 2014/2/27 15:06:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[add_value_to_black_user_list] @number_in varchar(50) as declare @number varchar(50) set @number=@number_in if len(@number)!=11     begin     print '警告！号码“'+@number+'”不是正确的手机号码！请重新输入！'     return     end else     begin     if exists(select * from dbo.STATIC_Black_User_List where number=@number)         begin         print '插入失败！手机号码“'+@number+'”已存在于黑名单中！'         return         end     else         begin         insert into dbo.STATIC_Black_User_List values(@number)         print '插入成功！已将手机号码“'+@number+'”插入黑名单！'         end        end 调用方法 declare @number varchar(50) set @number='' exec dbo.add_value_to_black_user_list @number 已呼号码维护 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[import_called_number_from_csv]    Script Date: 2014/2/27 15:06:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[import_called_number_from_csv] @csv_file_name_in varchar(50), @csv_file_path_in varchar(50), @destination_all_called_table_name_in varchar(50) as declare @csv_file_name varchar(50) set @csv_file_name=@csv_file_name_in declare @csv_file_path varchar(50) set @csv_file_path=@csv_file_path_in declare @destination_all_called_table_name varchar(50) set @destination_all_called_table_name=@destination_all_called_table_name_in if @csv_file_name=''     begin     print '警告！请先输入CSV文件名！'     return     end if @csv_file_path=''     begin     print '警告！请先输入CSV文件路径！ 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 为“X:\PATH”！'     return     end if @destination_all_called_table_name=''     begin     print '警告！请先输入希望创建的已呼号码 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 的表名！'     return     end if @csv_file_name!='' and @csv_file_path!='' and @destination_all_called_table_name!=''     begin     declare @sql_cmd_create_temporary_table nvarchar(1000)     declare @temporary_table varchar(50)     set @temporary_table='temporary_table'     set @sql_cmd_create_temporary_table='select * into'+' '+@temporary_table+' '+'from OpenDataSource('+'''Microsoft.ACE.OLEDB.12.0'''+','+'''Text;HDR=Yes;DATABASE='+@csv_file_path+''')...['+@csv_file_name+'#csv]'     print @sql_cmd_create_temporary_table     exec sp_executesql @sql_cmd_create_temporary_table     declare @sql_cmd_create nvarchar(1000)     set @sql_cmd_create='create table'+' '+@destination_all_called_table_name+' '+'(number varchar(255))'     declare @sql_cmd_insert nvarchar(1000)     set @sql_cmd_insert='insert into'+' '+@destination_all_called_table_name+' '+'select right(ACCEPTPHONE,11) from'+' '+@temporary_table+' '+'where datediff(d,logdate,getdate())<90'     declare @sql_cmd_delete nvarchar(1000)     set @sql_cmd_delete='delete from'+' '+@destination_all_called_table_name     if not exists (select * from sysobjects where name=@destination_all_called_table_name and type='U')         begin         print @sql_cmd_create         exec sp_executesql @sql_cmd_create         print @sql_cmd_insert         exec sp_executesql @sql_cmd_insert         end     else         begin         print @sql_cmd_delete         exec sp_executesql @sql_cmd_delete         print @sql_cmd_insert         exec sp_executesql @sql_cmd_insert         end     end     declare @sql_cmd_drop nvarchar(1000)     set @sql_cmd_drop='drop table'+' '+@temporary_table     print @sql_cmd_drop     exec sp_executesql @sql_cmd_drop 调用方法 declare @csv_file_name varchar(50) declare @csv_file_path varchar(50) declare @destination_all_called_table_name varchar(50) set @csv_file_name='20140225' set @csv_file_path='E:\Databases' set @destination_all_called_table_name='SUMMARIZATION_All_Called_Number' exec import_called_number_from_csv @csv_file_name,@csv_file_path,@destination_all_called_table_name 不能购买流量包的套餐列表维护 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[add_value_to_can_not_buy_traffic_package_list]    Script Date: 2014/2/27 15:07:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[add_value_to_can_not_buy_traffic_package_list] @t_name_in varchar(50) as declare @t_name varchar(50) set @t_name=@t_name_in if @t_name=''     begin     print '警告！请先输入不能购买流量包的套餐的套餐名或%套餐名%！'     return     end if @t_name!='' begin     if exists(select * from dbo.STATIC_Can_Not_Buy_Traffic_Package_List where t_name=@t_name)         begin         print '插入失败！套餐类型“'+@t_name+'”已存在于不能购买流量包定义列表中！'         return         end     else         begin         insert into dbo.STATIC_Can_Not_Buy_Traffic_Package_List values(@t_name)         print '插入成功！套餐类型“'+@t_name+'”已插入不能购买流量包定义列表！'         end end alter table dbo.STATIC_Can_Not_Buy_Traffic_Package_List drop column id alter table dbo.STATIC_Can_Not_Buy_Traffic_Package_List add id int identity(1,1) 调用方法 declare @t_name varchar(50) select @t_name='' exec dbo.add_value_to_can_not_buy_traffic_package_list @t_name 需屏蔽套餐列表维护 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[add_value_to_do_not_wanna_display_List]    Script Date: 2014/2/27 15:08:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[add_value_to_do_not_wanna_display_List] @t_name_in varchar(50) as declare @t_name varchar(50) set @t_name=@t_name_in if @t_name=''     begin     print '警告！请先输入需屏蔽套餐的套餐名或%套餐名%！'     return     end if @t_name!='' begin     if exists(select * from dbo.STATIC_Do_Not_Wanna_Display_List where t_name=@t_name)         begin         print '插入失败！套餐类型“'+@t_name+'”已存在于需屏蔽套餐定义列表中！'         return         end     else         begin         insert into dbo.STATIC_Do_Not_Wanna_Display_List values(@t_name)         print '插入成功！套餐类型“'+@t_name+'”已插入需屏蔽套餐定义列表！'         end end alter table dbo.STATIC_Do_Not_Wanna_Display_List drop column id alter table dbo.STATIC_Do_Not_Wanna_Display_List add id int identity(1,1) 调用方法 declare @t_name varchar(50) select @t_name='' exec dbo.add_value_to_do_not_wanna_display_List @t_name CRM源CSV文件导入 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[import_crm_source_data_from_csv]    Script Date: 2014/2/27 15:08:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[import_crm_source_data_from_csv] @csv_file_name_in varchar(50), @csv_file_path_in varchar(50), @destination_table_name_in varchar(50) as declare @csv_file_name varchar(50) set @csv_file_name=@csv_file_name_in declare @csv_file_path varchar(50) set @csv_file_path=@csv_file_path_in declare @destination_table_front_charactor_name varchar(50) set @destination_table_front_charactor_name=@destination_table_name_in if @csv_file_name=''     begin     print '警告！请输入请输入CSV源文件的文件名！'     return     end if @csv_file_path=''     begin     print '警告！请先输入CSV文件路径！格式为“X:\PATH”！'     return     end if @destination_table_front_charactor_name=''     begin     print '警告！请输入自定义目标数据表的表名前缀！'     return     end if @csv_file_name!='' and @csv_file_path!='' and @destination_table_front_charactor_name!=''     begin     declare @today varchar(20)     select @today=(select convert(varchar(10),GETDATE(),112))     declare @sql_cmd_create nvarchar(1000)     set @sql_cmd_create='select * into'+' '+@destination_table_front_charactor_name+@today+' '+'from OpenDataSource('+'''Microsoft.ACE.OLEDB.12.0'''+','+'''Text;HDR=Yes;DATABASE=E:\Databases'''+')...['+@csv_file_name+'#csv]'     declare @sql_cmd_drop_table nvarchar(1000)     set @sql_cmd_drop_table='drop table'+' '+@destination_table_front_charactor_name+@today         if not exists (select * from sysobjects where name=@destination_table_front_charactor_name+@today and type='U')         begin         print @sql_cmd_create         exec sp_executesql @sql_cmd_create         print '自定义目标数据表'+@destination_table_front_charactor_name+@today+'创建完成！'         end     else         begin         print @sql_cmd_drop_table         exec sp_executesql @sql_cmd_drop_table         print '自定义目标数据表'+@destination_table_front_charactor_name+@today+'删除成功！'                 print @sql_cmd_create         exec sp_executesql @sql_cmd_create         print '自定义目标数据表'+@destination_table_front_charactor_name+@today+'创建完成！'         end     end 调用方法 declare @csv_file_name varchar(50) declare @csv_file_path varchar(50) declare @destination_table_name varchar(50) set @csv_file_name='Source_CRM_20140219' set @csv_file_path='E:\Databases' set @destination_table_name='SOURCE_CRM_' exec dbo.import_crm_source_data_from_csv @csv_file_name,@csv_file_path,@destination_table_name 按 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf 剔除 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[exclude_condition_delete_from_analysis_crm_table]    Script Date: 2014/2/27 15:08:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[exclude_condition_delete_from_analysis_crm_table] @source_crm_table_name_in varchar(50), @destination_table_front_charactor_name_in varchar(50), @black_user_number_list_table_name_in varchar(50), @all_called_number_list_table_name_in varchar(50), @can_not_buy_traffic_package_tname_list_table_name_in varchar(50), @do_not_wanna_display_tname_list_table_name_in varchar(50) as declare @source_crm_table_name varchar(50) set @source_crm_table_name=@source_crm_table_name_in declare @destination_table_front_charactor_name varchar(50) set @destination_table_front_charactor_name=@destination_table_front_charactor_name_in declare @black_user_number_list_table_name varchar(50) set @black_user_number_list_table_name=@black_user_number_list_table_name_in declare @all_called_number_list_table_name varchar(50) set @all_called_number_list_table_name=@all_called_number_list_table_name_in declare @can_not_buy_traffic_package_tname_list_table_name varchar(50) set @can_not_buy_traffic_package_tname_list_table_name=@can_not_buy_traffic_package_tname_list_table_name_in declare @do_not_wanna_display_tname_list_table_name varchar(50) set @do_not_wanna_display_tname_list_table_name=@do_not_wanna_display_tname_list_table_name_in if @source_crm_table_name=''     begin     print '警告！请先输入正确的CRM源表名！'     return     end     if @destination_table_front_charactor_name=''     begin     print '警告！请输入自定义目标数据表的表名前缀！'     return     end     if @black_user_number_list_table_name=''or not exists (select * from sysobjects where name=@black_user_number_list_table_name and type='U')     begin     print '警告！请先输入正确的黑名单表名！'     return     end     if @all_called_number_list_table_name='' or not exists (select * from sysobjects where name=@all_called_number_list_table_name and type='U')     begin     print '警告！请先输入正确的已呼号码表表名！'     return     end if @can_not_buy_traffic_package_tname_list_table_name='' or not exists (select * from sysobjects where name=@can_not_buy_traffic_package_tname_list_table_name and type='U')     begin     print '警告！请先输入不能购买流量包套餐列表的表名！'     return     end if @do_not_wanna_display_tname_list_table_name='' or not exists (select * from sysobjects where name=@do_not_wanna_display_tname_list_table_name and type='U')     begin     print '警告！请先输入需屏蔽套餐列表的表名！'     return     end if @source_crm_table_name!='' and @destination_table_front_charactor_name!='' and @black_user_number_list_table_name!='' and @all_called_number_list_table_name!='' and @can_not_buy_traffic_package_tname_list_table_name!='' and @do_not_wanna_display_tname_list_table_name!=''     begin     --!从原始表建分析表，并用当日日期为后缀     declare @today varchar(20)     select @today=(select convert(varchar(10),GETDATE(),112))     declare @sql_cmd_create_analysis_crm_table nvarchar(1000)     set @sql_cmd_create_analysis_crm_table='select *,dbo.extract_number_from_the_charactor_string(limit_lowest_pay) as limit_lowest_pay_value into'+' '+@destination_table_front_charactor_name+@today+' '+'from'+' '+@source_crm_table_name     declare @sql_cmd_drop_table nvarchar(1000)     set @sql_cmd_drop_table='drop table'+' '+@destination_table_front_charactor_name+@today     if not exists (select * from sysobjects where name=@destination_table_front_charactor_name+@today and type='U')         begin         print @sql_cmd_create_analysis_crm_table         exec sp_executesql @sql_cmd_create_analysis_crm_table         print '自定义目标数据表'+@destination_table_front_charactor_name+@today+'创建完成！'         end     else         begin         print @sql_cmd_drop_table         exec sp_executesql @sql_cmd_drop_table         print '自定义目标数据表'+@destination_table_front_charactor_name+@today+'删除成功！'                 print @sql_cmd_create_analysis_crm_table         exec sp_executesql @sql_cmd_create_analysis_crm_table         print '自定义目标数据表'+@destination_table_front_charactor_name+@today+'创建完成！'         end     --!参照黑名单表，进行数据剔除     declare @sql_cmd_delete_number_in_black_user_list_table nvarchar(1000)     set @sql_cmd_delete_number_in_black_user_list_table='delete from dbo.'+@destination_table_front_charactor_name+@today+' '+'where number in (select number from'+' '+@black_user_number_list_table_name+')'     print @sql_cmd_delete_number_in_black_user_list_table     exec sp_executesql @sql_cmd_delete_number_in_black_user_list_table     --!参照已呼号码表，进行数据剔除     declare @sql_cmd_delete_number_in_all_called_number_list_table nvarchar(1000)     set @sql_cmd_delete_number_in_all_called_number_list_table='delete from dbo.'+@destination_table_front_charactor_name+@today+' '+'where number in (select number from'+' '+@all_called_number_list_table_name+')'     print @sql_cmd_delete_number_in_all_called_number_list_table     exec sp_executesql @sql_cmd_delete_number_in_all_called_number_list_table         --!参照不能购买流量包套餐列表，进行数据剔除     declare @counter int     set @counter=1     declare @sql_cmd_delete_number_condition_is_tname_in_can_not_buy_traffic nvarchar(1000)     set @sql_cmd_delete_number_condition_is_tname_in_can_not_buy_traffic='delete from dbo.'+@destination_table_front_charactor_name+@today+' '+'where t_name like (select t_name from'+' '+@can_not_buy_traffic_package_tname_list_table_name+' '+'where id='+convert(varchar(11),@counter)+')'     declare @sql_cmd_get_maxid nvarchar(1000)     declare @maxid int     set @sql_cmd_get_maxid='select @outlet=(select max(id) from'+' '+@can_not_buy_traffic_package_tname_list_table_name+')'     exec sp_executesql @sql_cmd_get_maxid,N'@outlet as int output',@outlet=@maxid output     print '表'+@can_not_buy_traffic_package_tname_list_table_name+'的最大ID为'+convert(varchar(11),@maxid)+'！'     declare @sql_cmd_delete_condition_tname_like nvarchar(1000)     while     @counter<=@maxid         begin         set @sql_cmd_delete_condition_tname_like='delete from dbo.'+@destination_table_front_charactor_name+@today+' '+'where t_name like (select t_name from'+' '+@can_not_buy_traffic_package_tname_list_table_name+' '+'where id='+convert(varchar(11),@counter)+')'         print @sql_cmd_delete_condition_tname_like         exec sp_executesql @sql_cmd_delete_condition_tname_like         set @counter=@counter+1         end     set @counter=1         --!参照需屏蔽套餐列表，进行数据剔除     set @sql_cmd_get_maxid='select @outlet=(select max(id) from'+' '+@do_not_wanna_display_tname_list_table_name+')'     exec sp_executesql @sql_cmd_get_maxid,N'@outlet as int output',@outlet=@maxid output     print '表'+@do_not_wanna_display_tname_list_table_name+'的最大ID为'+convert(varchar(11),@maxid)+'！'     while     @counter<=@maxid         begin         set @sql_cmd_delete_condition_tname_like='delete from dbo.'+@destination_table_front_charactor_name+@today+' '+'where t_name like (select t_name from'+' '+@do_not_wanna_display_tname_list_table_name+' '+'where id='+convert(varchar(11),@counter)+')'         print @sql_cmd_delete_condition_tname_like         exec sp_executesql @sql_cmd_delete_condition_tname_like         set @counter=@counter+1         end     set @counter=1     end 调用方法 declare @source_crm_table_name varchar(50) declare @destination_table_front_charactor_name varchar(50) declare @black_user_number_list_table_name varchar(50) declare @all_called_number_list_table_name varchar(50) declare @can_not_buy_traffic_package_tname_list_table_name varchar(50) declare @do_not_wanna_display_tname_list_table_name varchar(50) set @source_crm_table_name='SOURCE_CRM_20140226' set @destination_table_front_charactor_name='ANALYSIS_CRM_' set @black_user_number_list_table_name='STATIC_Black_User_List' set @all_called_number_list_table_name='SUMMARIZATION_All_Called_Number' set @can_not_buy_traffic_package_tname_list_table_name='STATIC_Can_Not_Buy_Traffic_Package_List' set @do_not_wanna_display_tname_list_table_name='STATIC_Do_Not_Wanna_Display_List' exec dbo.exclude_condition_delete_from_analysis_crm_table @source_crm_table_name, @destination_table_front_charactor_name, @black_user_number_list_table_name, @all_called_number_list_table_name, @can_not_buy_traffic_package_tname_list_table_name, @do_not_wanna_display_tname_list_table_name 一个通用的维护用存储过程 USE [OutCallMarketing] GO /****** Object:  StoredProcedure [dbo].[insert_varchar_value_to_destination_table]    Script Date: 2014/2/27 15:09:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[insert_varchar_value_to_destination_table] @table_name_in varchar(255), @field_name_in varchar(255), @value_in varchar(255) as declare @table_name varchar(255) set @table_name=@table_name_in declare @field_name varchar(255) set @field_name=@field_name_in declare @value varchar(255) set @value=@value_in if not exists(select * from sysobjects where name=@table_name and type='U') begin print '警告！表“'+@table_name+'”不存在！' return end else     if not exists(select * from syscolumns where id=object_id(@table_name) and name=@field_name)     begin     print '警告！列“'+@field_name+'”不存在！'     return     end         if @value=''         begin         print '警告！请输入正确的值！'         return         end         else             declare @sql_cmd nvarchar(1000)             set @sql_cmd='insert into'+' '+@table_name+' '+'values('''+@value+''')'             declare @summation int             declare @sql_cmd_get_summation nvarchar(1000)             set @sql_cmd_get_summation='select @outlet=(select count(*) from'+' '+@table_name+' '+'where'+' '+@field_name+'='+''''+@value+''')'             --print @sql_cmd_get_summation             exec sp_executesql @sql_cmd_get_summation,N'@outlet as int output',@outlet=@summation output             if @summation!=0                 begin                 print '字符串“'+@value+'”在表“'+@table_name+'”中存在！数量为：“'+convert(varchar(11),@summation)+'”条！'                 return                 end             else                 begin                 print @sql_cmd                 --print @summation                 exec sp_executesql @sql_cmd                 print '字符串“'+@value+'”已成功插入“'+@table_name+'”的“'+@field_name+'”列中！'                 end                 alter table dbo.STATIC_Can_Not_Buy_Traffic_Package_List drop column id                 alter table dbo.STATIC_Can_Not_Buy_Traffic_Package_List add id int identity(1,1) 调用方法 declare @destination_table_name varchar(255) declare @field_name_of_destination_table varchar(255) declare @value_you_want_to_insert varchar(255) set @destination_table_name='STATIC_Can_Not_Buy_Traffic_Package_List' set @field_name_of_destination_table='t_name' set @value_you_want_to_insert='%天翼领航通信版%' exec dbo.insert_varchar_value_to_destination_table @destination_table_name,@field_name_of_destination_table,@value_you_want_to_insert 自定义提数函数 USE [OutCallMarketing] GO /****** Object:  UserDefinedFunction [dbo].[extract_number_from_the_charactor_string]    Script Date: 2014/2/27 15:09:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[extract_number_from_the_charactor_string](@string varchar(2000)) returns money as begin declare @counter int set @counter=0     declare @number_string varchar(1000) set @number_string='' while @counter<=len(@string)     begin         if substring(@string,@counter,1)>='0' and substring(@string,@counter,1)<='9'         begin         set @number_string=@number_string+substring(@string,@counter,1)         end         set @counter=@counter+1     end     return @number_string end 调用方法 declare @today varchar(20) select @today=(select convert(varchar(10),GETDATE(),112)) declare @sql_cmd_create_analysis_crm_table nvarchar(1000) set @sql_cmd_create_analysis_crm_table='select *,dbo.extract_number_from_the_charactor_string(limit_lowest_pay) as limit_lowest_pay_value into'+' '+@destination_table_front_charactor_name+@today+' '+'from'+' '+@source_crm_table_name print @sql_cmd_create_analysis_crm_table exec sp_executesql @sql_cmd_create_analysis_crm_table select dbo.extract_number_from_the_charactor_string('全省终端抵用券月最低消费20元（预后共用）')as function_result function_result 20.00 作者：郭晟，山西电信