I got an insert statement that works fine in the QA but when I put it in a Stored Procedure it doesn't work. I don't use Transact-SQL very much so I figure I'm missing something. Any tips would be appreciated.
Here's the code:
CREATE PROCEDURE [dbo].[migrate_data] AS
declare @days int
declare @vdays varchar(50)
declare days_cursor SCROLL CURSOR for
select setting from flags where type = 'TempJob days before purge'
open days_cursor
fetch absolute 1 from days_cursor into @vdays
set @days=CONVERT(int, @vdays)
close days_cursor
deallocate days_cursor
---------------------------------
begin transaction insert_records
---------------------------------
insert into archive
(
submitted,
model,
shop,
cc,
position,
job,
LineNumber,
ShimShop,
TotalShims,
TotalSize,
InProgress,
Completed,
MillUsedToCut,
Operator,
MaterialType
)
select
submitted,
model,
shop,
cc,
position,
job,
line,
ToShimShopID,
NumShims,
Area,
InProgress,
Completed,
Mill,
operator,
MaterialType
From TempJob
Where completed < (getdate()- @days) and status = 'completed'
----------------------------------
commit transaction insert_records
----------------------------------
Here's the code:
CREATE PROCEDURE [dbo].[migrate_data] AS
declare @days int
declare @vdays varchar(50)
declare days_cursor SCROLL CURSOR for
select setting from flags where type = 'TempJob days before purge'
open days_cursor
fetch absolute 1 from days_cursor into @vdays
set @days=CONVERT(int, @vdays)
close days_cursor
deallocate days_cursor
---------------------------------
begin transaction insert_records
---------------------------------
insert into archive
(
submitted,
model,
shop,
cc,
position,
job,
LineNumber,
ShimShop,
TotalShims,
TotalSize,
InProgress,
Completed,
MillUsedToCut,
Operator,
MaterialType
)
select
submitted,
model,
shop,
cc,
position,
job,
line,
ToShimShopID,
NumShims,
Area,
InProgress,
Completed,
Mill,
operator,
MaterialType
From TempJob
Where completed < (getdate()- @days) and status = 'completed'
----------------------------------
commit transaction insert_records
----------------------------------