September 11, 2008 at 12:19 am
can anyone provide a small script , with some update querry
if update succeeds then must print 'done'
and if no such row exists then print 'not such row found'
..can i use catch n try????
September 11, 2008 at 1:23 am
From the way you wrote your question I understand that by successful you mean that at least one row was effected and not that the update statement caused a runtime error. You can use the catch block only to catch runtime errors, but any SQL Statement that did not cause an error and did not effect any row is considered a successful statement and it will not trigger the catch block. Instead of using the catch block you can use the @@rowcount function. Here is an example:
UPDATE MyTbl SET MyCol = 1
IF @@ROWCOUNT = 0
PRINT 'done'
ELSE
PRINT 'no such row exist'
Notice that using print with applications can cause problems. Not all applications know how to work with print’s output.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2008 at 7:59 pm
Oh.... be careful... you didn't include anything to limit the update to a single row in the table. Your current query will update the entire table all the time!
It should be more like this...
UPDATE MyTbl SET MyCol = 1
WHERE somecol = somecriteria
IF @@ROWCOUNT = 0
PRINT 'done'
ELSE
PRINT 'no such row exist'
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2008 at 11:12 am
Flip those around 🙂
UPDATE MyTbl SET MyCol = 1
WHERE somecol = somecriteria
IF @@ROWCOUNT = 0
PRINT 'no such row exists'
ELSE
PRINT 'done and we are all happy happy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply