sproc help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sproc help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-07 : 11:21:29

Jim writes "Hello guys
I am sorry to ask here but I am not getting any joy with my script I am getting unexpected results in my itemsales_error table and I don't know why. Is the logic of my if statements correct please ?

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_updateJCTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_updateJCTest]
GO
USE lrdatawarehouse
GO
CREATE PROCEDURE dbo.sp_updateJCTest
AS
SET NOCOUNT ON
DECLARE @input char(56)
DECLARE @saledate smalldatetime
DECLARE @store smallint
DECLARE @prodcode char(6)
DECLARE @value dec(10,3)
DECLARE @qty integer
DECLARE @costvalue dec(10,3)
DECLARE @type smallint
DECLARE @isl char(1)
DECLARE @id smallint
DECLARE @prod2 char(6)
DECLARE @deleted char(1)
DECLARE @island char(1)
DECLARE @storetype char(3)
DECLARE @NTuser char(20)
DECLARE @perc dec(6,2)
DECLARE @timestamp smalldatetime
DECLARE @found char(1)
DECLARE @offerinput char(49)
DECLARE @ean char(14)
DECLARE @prd1 char(6)
DECLARE @packsize numeric
DECLARE @cases_qty dec(18,2)
DECLARE @error varchar(100)
DECLARE @indent as int
DECLARE cursor_itemsales CURSOR FOR
SELECT * FROM test_sales

OPEN cursor_itemsales

FETCH NEXT FROM cursor_itemsales INTO @input

WHILE @@fetch_status <> -1
BEGIN

-- allocate all variables a value
SET @saledate = cast(substring(@input, 5, 2) + '/' + substring(@input, 3, 2) + '/' + substring(@input, 1, 2) as smalldatetime)
SET @prodcode = cast(substring(@input, 19, 6) as char(6))
SET @store = cast (substring(@input, 10, 3)as smallint)
SET @value = cast(substring(@input, 25, 10) as dec(10,3))
SET @qty = cast(substring(@input, 35, 5) as integer)
SET @costvalue = cast( substring(@input, 46, 11) as dec(9,3))
SET @error = null
SELECT @type = (select store_type from retail2 where retail2.store_no = @store)

-- insert records into the parent table if there are no errors
IF @@error = 0
INSERT INTO itemsales_test
VALUES (@saledate, @store, @prodcode, @value, @qty, 0, @costvalue ,'N', @type , @packsize, @cases_qty)

-- feed the identity variable for the foreign key referrences and check retail price is not less than -10000
SET @indent = @@identity
IF @value < cast(-10000 as dec(10,3))
begin
SET @error = 'Retail value less than 10000'
INSERT INTO itemsales_error
VALUES (@indent, @error)
SET @error = null
end
--check the cost price is not less than -10000
ELSE IF @costvalue < cast(-10000 as dec(9,3))
begin
SET @error = 'Cost value is less than 10000'
INSERT INTO itemsales_error
VALUES (@indent, @error)
SET @error = null
end
--check that the cost price is not higher than the retail price
ELSE IF @value < @costvalue
begin
SET @error = 'Retail value is higher than Cost value'
INSERT INTO itemsales_error
VALUES (@indent, @error)
SET @error = null
end

--set variable for island and if not valid insert into error table
IF @store IN (117,190,105,114,104,183,188,109,182,189,103,186,111,185,113,123,110,191,187,180,112,181,108,118,184)
SET @isl = 'J'
ELSE IF @store IN (270,271,272,211,203,273,202,274,281,201,210,276,280,206,204,278,207,279,205,209,208)
SET @isl = 'G'
ELSE
begin
SET @error = 'Invalid Store Number'
INSERT INTO itemsales_error
VALUES (@indent, @error)
SET @error = null
end

-- check for product in product main table and ean table and set packsize
IF @isl = 'G'
begin<

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-07 : 12:12:53

hard to tell since not whole sp is there...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources