Chapter 6 PROGRAMMING SQL SERVER 2000 347 UPDATE
Chapter 6 PROGRAMMING SQL SERVER 2000 347 UPDATE mytable SET emptype = manager WHERE name = fred IF @@ROWCOUNT > 0 PRINT There were rows changed But what if no rows changed? How can you print a message for that? Use the ELSE part of the logic: UPDATE mytable SET emptype = manager WHERE name = fred IF @@ROWCOUNT > 0 PRINT There were rows changed ELSE PRINT There were no rows changed Note once again the use of indentation. This is another example of optional, traditional indentation that you should do to make your code more readable. Now imagine that you want to run the UPDATE statement only if Fred is already not a manager. You could write something like this: IF (SELECT emptype FROM mytable WHERE name = fred ) <> . manager UPDATE mytable SET emptype = manager WHERE name = . fred ELSE PRINT There were no rows changed It would be helpful if you could put more than one statement in there, to put that PRINT statement back. But unfortunately, the IF statement can take only one statement. The solution is to make several statements look like just one statement. IF (SELECT emptype FROM mytable WHERE name = fred ) <> . manager BEGIN UPDATE mytable SET emptype = manager WHERE name = . fred PRINT There were rows changed END ELSE PRINT There were no rows changed That s better. Note the use of BEGIN and END. An IF statement, like the WHILE statement that will be covered shortly, can operate on only one statement, so you need to use the BEGIN and END constructs to make it all look like one statement. A few more examples are probably in order. Imagine that you want to insert a new record only if there aren t any existing records that match certain criteria. You could write something like this: if NOT exists (SELECT * FROM mytable WHERE emptype = . manager ) continues
If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.