Using RAISERROR When Value Goes Above Certain Num? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

Using RAISERROR When Value Goes Above Certain Num?

Author

Topic

Palermo
Starting Member

25 Posts

Posted - 2013-10-24 : 17:29:58

I want to run a transaction query that raises the value in a field by a certain number e.g. 10 but outputs an error if the value in that field goes above say 50. I have :

SELECT 'BEFORE', ItemCode,fee
FROM db.items
WHERE ItemCode = 1
BEGIN TRAN IncreaseBy10
UPDATE db.items
SET fee = fee+10
WHERE ItemCode = 1
IF @@ROWCOUNT = 50
PRINT 'Item price is too high please reduce';
SELECT 'WITHIN', *
FROM db.items

This increases the item prices and now several are above 50 in ItemCode 1 but it does not output an error.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 17:38:21

You could simplify it like shown below

IF EXISTS
(
	SELECT * FROM db.Items
	WHERE fee > 40 AND ItemCode = 1
)
-- handle your error condition here
PRINT 'Prices too high'
ELSE
	UPDATE db.Items SET 
		fee = fee + 10
	WHERE
		ItemCode = 1;

I may have misunderstood your requirement; you are using @@rowcount - which counts the number of rows affected. Can there be more than one row with ItemCode = 1?

Palermo
Starting Member

25 Posts

Posted - 2013-10-24 : 19:06:26

quote:
Originally posted by James K

You could simplify it like shown below
IF EXISTS
(
SELECT * FROM db.Items
WHERE fee > 40 AND ItemCode = 1
)
-- handle your error condition here
PRINT 'Prices too high'
ELSE
UPDATE db.Items SET
fee = fee + 10
WHERE
ItemCode = 1;
I may have misunderstood your requirement; you are using @@rowcount - which counts the number of rows affected. Can there be more than one row with ItemCode = 1?

Yes there can be more than one row with the same item code. Could you show me how to do it by amending my code? I want to learn it this way before simplifying it as I think I will get a better understanding.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources