记自用的一条嵌入循环的存储过程

最新推荐文章于 2024-09-24 15:38:32 发布

jcyugz

最新推荐文章于 2024-09-24 15:38:32 发布

阅读量220

点赞数 1

分类专栏： MSSQL 文章标签：存储 parameters import 产品 date 2010

本文链接：https://blog.csdn.net/jcyugz/article/details/5148248

版权

MSSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

GO
/****** 对象: StoredProcedure [dbo].[Import] 脚本日期: 01/07/2010 10:03:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: J.CYu
-- Create date: 2010.01.07
-- Description: 配合C平台的产品搜索
-- =============================================
CREATE PROCEDURE [dbo].[Import]
-- Add the parameters for the stored procedure here

AS
    declare @num int
    set @num=0
    declare k_cursor cursor for -- 声明游标变量
    select [id],[keyword] from keyword_c
    declare @IDField int --声明临时存放ID的变量
    declare @keywordField nvarchar(100)
    open k_cursor -- 打开游标
    fetch next from k_cursor into @IDField,@keywordField

    while(@@fetch_status=0) --循环开始[关键字表]
       begin
           --内循环[产品表]
           declare p_cursor cursor for select [ID],[Keywords_C] from ProductInfo where
           (ProductName like '%'+@keywordField+'%' or
            SearchKey like '%'+@keywordField+'%' or
            CategoryName like '%'+@keywordField+'%' or
            BrandName like '%'+@keywordField+'%' or
            CatenaName like '%'+@keywordField+'%' or
            ProductModel like '%'+@keywordField+'%' or
            Stuff like '%'+@keywordField+'%' or
            ProducingArea like '%'+@keywordField+'%' or
            Description like '%'+@keywordField+'%' or
            EnterpriseName like '%'+@keywordField+'%'
            )
            declare @PIDField uniqueidentifier
            declare @Keywords_CField nvarchar(4000)
            open p_cursor
            fetch next from p_cursor into @PIDField,@Keywords_CField
            while(@@fetch_status=0)
              begin
                 --Update 语句、或做其它操作
                 print @Keywords_CField
                 update ProductInfo
                 set Keywords_C= @keywordField+'|'+Keywords_C
                 where ID=@PIDField
                 fetch next from p_cursor into @PIDField,@Keywords_CField
                 set @num=@num+1
              end
             close p_cursor
             deallocate p_cursor

            fetch next from k_cursor into @IDField,@keywordField
        end

   close k_cursor --关闭游标
   deallocate k_cursor --释放游标引用
   print '共更新数据'+@num+'次'

=====================Update=======================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: YWC
-- Create date: 2010.01.07
-- Description: 配合C平台的产品搜索
-- =============================================
ALTER PROCEDURE [dbo].[Import_New]
-- Add the parameters for the stored procedure here

AS
declare p_cursor cursor for -- 声明游标变量
select ID,(isNull(ProductName,'')+'|'+isNull(SearchKey,'')+'|'+isNull(CategoryName,'')+'|'+isNull(BrandName,'')+'|'+isNull(CatenaName,'')+'|'+isNull(ProductModel,'')+'|'+isNull(Stuff,'')+'|'+isNull(ProducingArea,'')+'|'+isNull(cast(Description as nvarchar(4000)),'')+'|'+isNull(EnterpriseName,'')) as Keys from productinfo
declare @IDField uniqueidentifier --声明临时存放ID的变量
declare @keys nvarchar(4000)
open p_cursor -- 打开游标
fetch next from p_cursor into @IDField,@keys

while(@@fetch_status=0) --循环开始[关键字表]
  begin
           --内循环[产品表]
            declare @updateStr nvarchar(2000)
            set @updateStr=''
            declare k_cursor cursor for select keyword from keyword_search
            declare @keywordField nvarchar(50)
            open k_cursor
            fetch next from k_cursor into @keywordField
            while(@@fetch_status=0)
              begin
                 if charindex(@keywordField,@keys)>0
                 begin
                 if @updateStr=''
                 set @updateStr=@keywordField
                 else
                 set @updateStr=@keywordField+'|'+@updateStr
                 end
                 fetch next from k_cursor into @keywordField
              end
             close k_cursor
             deallocate k_cursor
             update ProductInfo
             set Keywords= @updateStr
             where ID=@IDField
            fetch next from p_cursor into @IDField,@keys
        end

close p_cursor --关闭游标
deallocate p_cursor --释放游标引用