I have not done anything related to this knowledge before, so this time I have met with many questions and problems when I have to use store procedure to finish some database operation. In order to make myself know more, I note down some thing here firstly and I would try to refine these materials to a real note if I had sometime in future.
CREATE PROCEDURE [dbo].[zhangke]
AS
BEGIN
declare @tempOfferId char(50)
declare @topCats varchar(200)
declare @subCats varchar(300)
DECLARE rs CURSOR LOCAL SCROLL FOR
select offer_id from compare_task_detail_result
open rs
fetch next from rs into @tempOfferId
WHILE @@FETCH_STATUS = 0
Begin
select @topCats = Category,@subCats = ItemProviderCategory from [JellyFish].[dbo].[20080922] where Id=@tempOfferId
update compare_task_detail_result
set offer_category=@topCats,offer_ItemProviderCategory=@subCats
where offer_id = @tempOfferId
fetch next from rs into @tempOfferId
end
close rs
END
USE [temp]
GO
/****** Object: StoredProcedure [dbo].[updateTempTable] Script Date: 01/06/2009 12:57:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[updateTempTable]
@tbname varchar(100)
AS
BEGIN
declare @tempOfferId char(50)
declare @topCats varchar(200)
declare @subCats varchar(300)
declare @sqlStr nvarchar(1000)
DECLARE rs CURSOR LOCAL SCROLL FOR
select offer_id from compare_task_detail_result
open rs
fetch next from rs into @tempOfferId
WHILE @@FETCH_STATUS = 0
Begin
set @sqlStr=' select @topCats = Category,@subCats = ItemProviderCategory from ' + @tbname + ' where Id='+@tempOfferId;
EXEC SP_EXECUTESQL @sqlStr,N'@topCats varchar(200) out,@subCats varchar(300) out', @topCats out,@subCats out
update compare_task_detail_result
set offer_category=@topCats,offer_ItemProviderCategory=@subCats
where offer_id = @tempOfferId
fetch next from rs into @tempOfferId
end
close rs
END
USE [temp]
GO
/****** Object: StoredProcedure [dbo].[updateTempTable2] Script Date: 01/06/2009 16:39:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[updateTempTable2]
--@tbname varchar(1000)
AS
BEGIN
declare @tempOfferId char(50)
declare @topCats varchar(200)
declare @subCats varchar(300)
declare @sqlStr nvarchar(1000)
DECLARE rs CURSOR LOCAL SCROLL FOR
select offer_id from compare_task_detail_result
open rs
fetch next from rs into @tempOfferId
WHILE @@FETCH_STATUS = 0
Begin
--set @sqlStr=' select @topCatsOut = Category,@subCatsOut = ItemProviderCategory from ' + @tbname + ' where Id='+@tempOfferId;
--EXEC SP_EXECUTESQL @sqlStr,N'@topCatsOut varchar(200) OUTPUT,@subCatsOut varchar(300) OUTPUT', @topCatsOut = @topCats OUTPUT,@subCatsOut = @subCats OUTPUT
select @topCats = Category,@subCats = ItemProviderCategory from [JellyFish].[dbo].[20080922] where Id=@tempOfferId
update compare_task_detail_result
set offer_category=@topCats,offer_ItemProviderCategory=@subCats
where offer_id = @tempOfferId
fetch next from rs into @tempOfferId
end
close rs
END
