November 22, 2005 at 11:00 am
Hi Guys, I'm struggling with how to set my variable to the value in a one of my columns in a cursor i've got open. Here is the procedure I've got at the moment. I've put the lines that are failing in red. I've tried with the table name and the cursor name preceding the column name but to no avail.
CREATE PROCEDURE [dbo].[proc_O2_Add_SPID_TariffID]
AS
Declare @strRecordType VARCHAR(3) --This is the A, B or C value in RecordType Column
Declare @lngSpid SMALLINT --This contains the SpID number that is identified in Record Length Column when Record Type = A
Declare @strTariffID VARCHAR(3) --This contains the Tariff ID that is identified in Record Length when Record Type = B
Declare @strTariffName VARCHAR(50) -- This contains the Tariff Name that is identified in Mobile Number when Record Type = B
SET NOCOUNT ON
SET ANSI_PADDING ON
Declare rstO2NetworkBase CURSOR Forward_Only
FOR
SELECT SPID, TariffID, TariffName, MobileNo, RecordLength
FROM tbl_O2_Base_NW_Import
OPEN rstO2NetworkBase
FETCH NEXT FROM rstO2NetworkBase
WHILE @@FETCH_STATUS = 0
BEGIN
Set @strRecordType = [RecordLength]
If @strRecordType = 'A'
Begin
Set @lngSpid = [RecordLength]
Set @strTariffID = 'XXX'
Set @strTariffName = 'Header'
end
If @strRecordType = 'B'
begin
Set @strTariffID = 'Change'
Set @strTariffID = [RecordLength]
If @strTariffID = '0'
begin
Set @strTariffName = 'ZeroZeroZero'
end
Else
begin
set @strTariffName = [MobileNo]
end
end
UPDATE tbl_O2_Base_NW_Import
SET tbl_O2_Base_NW_Import.SPID = @strRecordType, tbl_O2_Base_NW_Import.TariffID = @strTariffID, tbl_O2_Base_NW_Import.TariffName = @strTariffName
WHERE CURRENT OF rstO2NetworkBase
FETCH NEXT FROM rstO2NetworkBase
End
CLOSE rstO2NetworkBase
DEALLOCATE rstO2NetworkBase
SET NOCOUNT OFF
GO
November 22, 2005 at 12:26 pm
CREATE PROCEDURE [dbo].[proc_O2_Add_SPID_TariffID]
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
DECLARE @SPID INT
DECLARE @TariffID VARCHAR(3)
DECLARE @TariffName VARCHAR(50)
DECLARE @MobileNo VARCHAR(25)
DECLARE @RecordLength INT
Declare rstO2NetworkBase CURSOR Forward_Only
FOR
SELECT SPID, TariffID, TariffName, MobileNo, RecordLength
FROM tbl_O2_Base_NW_Import
OPEN rstO2NetworkBase
FETCH NEXT FROM rstO2NetworkBase INTO @SPID, @TariffID, @TariffName, @MobileNo, @RecordLength
WHILE @@FETCH_STATUS = 0
BEGIN
/*
Do your work here
*/
SELECT @SPID, @TariffID, @TariffName, @MobileNo, @RecordLength
FETCH NEXT FROM rstO2NetworkBase INTO @SPID, @TariffID, @TariffName, @MobileNo, @RecordLength
End
CLOSE rstO2NetworkBase
DEALLOCATE rstO2NetworkBase
SET NOCOUNT OFF
GO
Regards,
gova
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply