Update Price Field - 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)

Update Price Field

Author

Topic

JLXL
Starting Member

2 Posts

Posted - 2006-03-08 : 03:44:06

I have a sql server database with a table the contains:
-Part Numbers (ptNumber) varchar
-Part Name (ptName) varchar
-Part Price (ptUnitPrice) Money
-Part is Active (ptActive) Bit

named tblPart. I am trying to create a stored procedure that updates the Part Price by a certain percentage if the part is not active (for testing, it will be for active parts later on)

Here is the t-sql statement I am working with now:


CREATE PROCEDURE procUpdateActive
		(
		@PercUpdate as Decimal
		)
AS
BEGIN
	DECLARE @LocalRows as int,
		@LocalError as int,
		@ErrMsg as varchar(256),
		@NewID as int

SET NOCOUNT ON
IF @PercUpdate IS NULL
		BEGIN
		SELECT @ErrMsg = 'Percent to Update by Is Required' 
		SELECT NewID=0, Error=1,'RowCount'=0,ErrMsg=@ErrMsg
		RETURN
		END


	BEGIN TRANSACTION

	UPDATE tblPart
	SET ptUnitPrice = ptUnitPrice+(ptUnitPrice*@PercUpdate)
	WHERE ptActive=0

	SELECT @LocalError=@@Error ,@LocalRows=@@RowCount
	
	IF NOT @LocalError = 0 OR @LocalRows=0  
		BEGIN
		ROLLBACK TRANSACTION
		SELECT @ErrMsg = 'An error occured somewhere?' 
		SELECT NewId=0, Error=@LocalError,'RowCount'=@LocalRows,ErrMsg=@ErrMsg
		END
	ELSE
		BEGIN	
		COMMIT TRANSACTION
		SELECT NewID=0,Error=0,'RowCount'=@LocalRows,ErrMsg=''
		END	
END

and then executing like this:


procUpdateActive .025

When I execute, it sets the price to zero instead of adding the percentange onto the existing, I tried changing datatypes for the @PerceUpdate arguement, but still got either a change to 0, or no update at all.

Any assistance anyone cna provide is greatly appreciated.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-08 : 03:58:54

change the parameter to float i guess it should work for you..

Alter PROCEDURE procUpdateActive
(
@PercUpdate as float -- Changes by Chirag from orginal..
)
AS
BEGIN
DECLARE @LocalRows as int,
@LocalError as int,
@ErrMsg as varchar(256),
@NewID as int

SET NOCOUNT ON
IF @PercUpdate IS NULL
BEGIN
SELECT @ErrMsg = 'Percent to Update by Is Required'
SELECT NewID=0, Error=1,'RowCount'=0,ErrMsg=@ErrMsg
RETURN
END

BEGIN TRANSACTION

UPDATE tblPart
SET ptUnitPrice = ptUnitPrice+(ptUnitPrice*@PercUpdate)
WHERE ptActive=0

SELECT @LocalError=@@Error ,@LocalRows=@@RowCount

IF NOT @LocalError = 0 OR @LocalRows=0
BEGIN
ROLLBACK TRANSACTION
SELECT @ErrMsg = 'An error occured somewhere?'
SELECT NewId=0, Error=@LocalError,'RowCount'=@LocalRows,ErrMsg=@ErrMsg
END
ELSE
BEGIN
COMMIT TRANSACTION
SELECT NewID=0,Error=0,'RowCount'=@LocalRows,ErrMsg=''
END
END

Sucess Comes to those who Believe in Beauty of their Dream..

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-08 : 04:00:07

Use isnull fuction before you do the mathematical operation. Some fields might have null value on it. Thus nothing happens to the updation.

Want Philippines to become 1st World COuntry? Go for World War 3...

JLXL
Starting Member

2 Posts

Posted - 2006-03-08 : 04:15:35

Float is was.......I appreciate both the replies and I am sure I will be back as tsql and Sql server is fairly new to me, so expect more questions in the future.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources