June 29, 2006 at 9:56 pm
Hi All,
I am new to cursors and by default having difficulty with the cursor, below. I want to pass through each record in the cursor one by one, each time generating a new key within table(timPricing). I can only get it to generate a key for the first record in the cursor.
Thanks,
Jake
DECLARE Pricing_cursor INSENSITIVE CURSOR FOR
SELECT PM.ItemId,PM.Price,tim.ItemId,tim.Itemkey
From PMPricing PM with (nolock)
Join timItem tim with (nolock)
on PM.ItemId = tim.ItemId
where tim.Companyid = 'soa'
Declare @PricingKey Int
Open Pricing_Cursor
Fetch Next from Pricing_Cursor
begin
exec spGetNextSurrogateKey 'timPricing', @PricingKey output
Insert into timPricing (PricingKey, Breaktype, PriceBase,PriceMeth,SubjToRebate)
values(@Pricingkey,1,0,5,0)
if @@error <> 0
begin
rollback transaction
end
else
While @@Fetch_Status = 0
Begin
Fetch Next from Pricing_cursor
begin
exec spGetNextSurrogateKey 'timPricing', @PricingKey output
Insert into timPricing (PricingKey, Breaktype, PriceBase,PriceMeth,SubjToRebate)
values(@Pricingkey,1,0,5,0)
if @@error <> 0
begin
rollback transaction
end
end
end
end
Close Pricing_Cursor
Deallocate Pricing_Cursor
June 29, 2006 at 11:17 pm
There are a number of errors with your logic above, I don't know where to start. I really believe you can do without cursor. Use code something like my suggestion below
BEGIN TRAN
INSERT INTO timPricing
(
PricingKey,
Breaktype,
PriceBase,
PriceMeth,
SubjToRebate
)
SELECT dbo.fnGetNextSurrogateKey('timPricing'),
1,
0,
5,
0
From PMPricing PM
INNER JOIN timItem tim ON PM.ItemId = tim.ItemId
where tim.Companyid = 'soa'
IF @error = 0
commit tran
else
rollback tran
What do you think?
N 56°04'39.16"
E 12°55'05.25"
June 30, 2006 at 4:25 am
Hi Peter,
Thanks for the suggestion, however, I noticed that you have modified 'exec spGetNextSurrogateKey..' to
'Select dbo.fnGetNextSurrogateKey...' Do I need to create this fn? If I run your script against my db I get error message: Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.fnGetNextSurrogateKey'.
Thanks,
Jake
June 30, 2006 at 5:15 am
I suggested you should rewrite your stored procedure that returns the surrogate key to you, as a function instead. So I called the function fnGetNextSurrogateKey instead.
Post the code for your spGetNextSurrogateKey stored procedure here, that creates the surrogate key, and I will take a look at it how to convert to function.
N 56°04'39.16"
E 12°55'05.25"
June 30, 2006 at 5:47 am
Here it is.
Thanks,
Jake
create procedure spGetNextSurrogateKey @iTableName VARCHAR(30),
@oNewKey int output
As
begin transaction
UPDATE tciSurrogateKey
SET NextKey = CASE NextKey
WHEN 2147483647 THEN 1
ELSE NextKey + 1
END
WHERE TableName = @iTableName
IF @@rowcount = 0
BEGIN
INSERT tciSurrogateKey
(TableName
,NextKey)
VALUES(@iTableName
,2)
END
SELECT @oNewKey = CASE NextKey
WHEN 1 THEN 2147483647
ELSE NextKey - 1
END
FROM tciSurrogateKey
WHERE TableName = @iTableName
commit transaction
RETURN
GO
June 30, 2006 at 6:17 am
Well. That is something I don't se very often these days. An ID storage table. So I don't know if you benefit from my suggestions, but here is a SET-based shot at it.
-- Prepare staging table
DECLARE @Stage TABLE
(
PricingKey INT IDENTITY(0, 1),
Breaktype TINYINT,
PriceBase TINYINT,
PriceMeth TINYINT,
SubjToRebate TINYINT
)
-- Copy the data to be inserted later
INSERT INTO @Stage
(
Breaktype,
PriceBase,
PriceMeth,
SubjToRebate
)
SELECT 1,
0,
5,
0
FROM PMPricing PM
INNER JOIN timItem tim ON PM.ItemId = tim.ItemId
WHERE tim.Companyid = 'soa'
-- Start working!
DECLARE @NextKey INT
-- Start a transaction for the whole shebang
BEGIN TRAN
-- Get the next key in line
SELECT @NextKey = NextKey
FROM tciSurrogateKey
WHERE TableName = 'timPricing'
-- If there is no key before, insert 1 as start value
IF @NextKey IS NULL
BEGIN
INSERT tciSurrogateKey
(
TableName,
NextKey
)
VALUES (
'timPricing',
1
)
SELECT @NextKey = 1
END
-- Do the real insert to destination table
INSERT INTO timPricing
(
PricingKey,
Breaktype,
PriceBase,
PriceMeth,
SubjToRebate
)
SELECT PricingKey + @NextKey,
Breaktype,
PriceBase,
PriceMeth,
SubjToRebate
FROM @Stage
UPDATE tciSurrogateKey
SET NextKey = NextKey + (SELECT COUNT(*) FROM @Stage)
WHERE TableName = 'timPricing'
IF @@ERRORS = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
This is probably not what you are after, but it will work. And I don't have nested TRANSACTION either.
N 56°04'39.16"
E 12°55'05.25"
June 30, 2006 at 7:25 am
Peter,
A thousand thanks! It looks and tests out nicely. I have a few minor issues that I think I can work out.
Jake
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply