Web Hosting Java Blog

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.

346 Part I EXAM PREPARATION later in Chapter 9 when you learn about user-defined functions and stored procedures. For now, you need to know what a RETURN does within a statement block. You can use it like this: BEGIN UPDATE mytable SET emptype = manager WHERE name = . fred RETURN UPDATE mytable SET name = george WHERE id = 42 END If you do that, the second UPDATE never runs. Not very useful now, but when you read through the next section on IF…THEN…ELSE, you ll understand. CONDITIONAL STATEMENTS AND BRANCHING WITH IF…THEN…ELSE So far, all the scripts you ve seen start at the top and go to the bottom. There s no way of conditionally executing any statements; all the statements execute, top down. That s not good. So SQL Server provides you with a couple of different ways to do something just in case something else is true. So, you want to actually write a batch that does something now? Need to insert a piece of data, but don t want to insert a duplicate? How about wanting to make sure that the data you re about to insert follows the rules for that type of data? Well, now you need to understand conditional execution and the IF…ELSE construct. In T-SQL, an IF statement looks like this: IF ELSE The expression has to be an expression that evaluates to a true or false condition, unlike some languages that use zero and non-zero. To evaluate something to true or false, you need to use the comparison operators the same comparison operators you used to compare things in SELECT statements from Chapter 4, Querying and Modifying Data. So, if you want to see whether your UPDATE statement actually changed any data, you could write something like this:

For high quality website hosting services please check java web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 345 STATEMENT BLOCKS WITH BEGIN…END The BEGIN and END keywords work together to group statements together. This is used in later constructs for loops and conditional statements, so although it may not make a lot of sense now, after the next three sections it will make more sense. BEGIN and END are used to create something called a statement block. A statement block is a group of statements that can be used anywhere one statement can be used. For example, you could write this: UPDATE mytable SET emptype = manager WHERE name = fred UPDATE mytable SET name = george WHERE id = 42 Or, if you wanted to, this would work: BEGIN UPDATE mytable SET emptype = manager WHERE name = . fred UPDATE mytable SET name = george WHERE id = 42 END Note that traditionally the indentation is the preferred style, and is not required, but the person who has to modify your code a few weeks after you write it will hunt you down and torture you if you don t indent properly. BEGIN and END must occur as a pair. That s why they are indented as they are: it makes it easy to spot if one of them is missing, and it makes it easy to tell where the statement block ends. You can nest statement blocks as follows: BEGIN UPDATE mytable SET emptype = manager WHERE name = . fred BEGIN PRINT Whoopee, a nested statement block! END UPDATE mytable SET name = george WHERE id = 42 END But there s no good reason to do that. There is no limit to the nesting, except that eventually you will run out of room to indent, and although that s not a technical limitation (SQL Server doesn t care) it s going to make it very hard to read the batch. There s one other thing to mention about the BEGIN…END statement. There s a special keyword called RETURN. The RETURN statement is very flexible you ll be using it in a lot of different ways here and

For high quality website hosting services please check cheap web hosting website.

344 Part I EXAM PREPARATION If you ve ever had a programming class, or ever read a programming blog, the teacher or author has probably emphasized the use of comments over and over again. It bears repeating. Using comments is the most reliable way of ensuring that you or anyone else can figure out what your code does. Comments are the most important part of the legacy you leave behind as a programmer. If you get hit by a truck, and don t leave any (or enough) comments in your code, it is probably going to be faster for someone to rewrite all of your code than to try and decipher what made perfect sense to you at the time. SQL Server has two methods for putting comments in your code. The first is to start the comment with a double dash (–). The double dash can appear anywhere on the line, and anything between the double dash and the end of the line is a comment and will not be executed. For example: –this is a comment on the whole line SET @i = 42 –this is a comment, but the preceding code .will execute –Nothing on this line executes SET @i = 21 The other style of comment, which is not seen as often anymore, is the slash-star comment. It works like this: /* This is a multi-line, slash-star style comment. note that this line is also part of the comment. This type of comment can end in the middle of a line */ SET @i = 42 In that case, the SET statement would be executed because it s outside the comment. One thing to watch for is that the string GO within a comment on a line by itself causes an error. So don t use the string GO in the comment. The more common convention by far is to use the double-dash style comment, and the new Query Analyzer for SQL Server 2000 provides you with an easy tool to create multi-line comments quickly and easily. Just highlight the lines you want to comment and press Control+Shift+C. This adds a double-dash to the beginning of each highlighted line. To uncomment the text, just use Control+Shift+R. This is a quick, easy, and painless way to comment out large chunks of code for testing and put them back later. There aren t any restrictions on any special words in the double-dash comment. That s how you make a bunch of statements look like none: by commenting statements out. But how can you make a bunch of statements look like one statement?

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

344 Part I EXAM PREPARATION If you ve ever had a programming class, or ever read a programming blog, the teacher or author has probably emphasized the use of comments over and over again. It bears repeating. Using comments is the most reliable way of ensuring that you or anyone else can figure out what your code does. Comments are the most important part of the legacy you leave behind as a programmer. If you get hit by a truck, and don t leave any (or enough) comments in your code, it is probably going to be faster for someone to rewrite all of your code than to try and decipher what made perfect sense to you at the time. SQL Server has two methods for putting comments in your code. The first is to start the comment with a double dash (–). The double dash can appear anywhere on the line, and anything between the double dash and the end of the line is a comment and will not be executed. For example: –this is a comment on the whole line SET @i = 42 –this is a comment, but the preceding code .will execute –Nothing on this line executes SET @i = 21 The other style of comment, which is not seen as often anymore, is the slash-star comment. It works like this: /* This is a multi-line, slash-star style comment. note that this line is also part of the comment. This type of comment can end in the middle of a line */ SET @i = 42 In that case, the SET statement would be executed because it s outside the comment. One thing to watch for is that the string GO within a comment on a line by itself causes an error. So don t use the string GO in the comment. The more common convention by far is to use the double-dash style comment, and the new Query Analyzer for SQL Server 2000 provides you with an easy tool to create multi-line comments quickly and easily. Just highlight the lines you want to comment and press Control+Shift+C. This adds a double-dash to the beginning of each highlighted line. To uncomment the text, just use Control+Shift+R. This is a quick, easy, and painless way to comment out large chunks of code for testing and put them back later. There aren t any restrictions on any special words in the double-dash comment. That s how you make a bunch of statements look like none: by commenting statements out. But how can you make a bunch of statements look like one statement?

For high quality website hosting services please check tomcat web hosting website.

344 Part I EXAM PREPARATION If you ve ever had a programming class, or ever read a programming blog, the teacher or author has probably emphasized the use of comments over and over again. It bears repeating. Using comments is the most reliable way of ensuring that you or anyone else can figure out what your code does. Comments are the most important part of the legacy you leave behind as a programmer. If you get hit by a truck, and don t leave any (or enough) comments in your code, it is probably going to be faster for someone to rewrite all of your code than to try and decipher what made perfect sense to you at the time. SQL Server has two methods for putting comments in your code. The first is to start the comment with a double dash (–). The double dash can appear anywhere on the line, and anything between the double dash and the end of the line is a comment and will not be executed. For example: –this is a comment on the whole line SET @i = 42 –this is a comment, but the preceding code .will execute –Nothing on this line executes SET @i = 21 The other style of comment, which is not seen as often anymore, is the slash-star comment. It works like this: /* This is a multi-line, slash-star style comment. note that this line is also part of the comment. This type of comment can end in the middle of a line */ SET @i = 42 In that case, the SET statement would be executed because it s outside the comment. One thing to watch for is that the string GO within a comment on a line by itself causes an error. So don t use the string GO in the comment. The more common convention by far is to use the double-dash style comment, and the new Query Analyzer for SQL Server 2000 provides you with an easy tool to create multi-line comments quickly and easily. Just highlight the lines you want to comment and press Control+Shift+C. This adds a double-dash to the beginning of each highlighted line. To uncomment the text, just use Control+Shift+R. This is a quick, easy, and painless way to comment out large chunks of code for testing and put them back later. There aren t any restrictions on any special words in the double-dash comment. That s how you make a bunch of statements look like none: by commenting statements out. But how can you make a bunch of statements look like one statement?

For high quality java hosting services please check java web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 343 If this value returns 128, then the flag was previously set, and ARITHIGNORE is turned on. If the value returns 0, then the flag was not set, and ARITHIGNORE is turned off. Other values that use this are the status value in the sysdatabases table and the status value in the sysobjects table. Using @@ROWCOUNT What s the fuss with @@ROWCOUNT? It just returns the number of rows affected by the previous statement. Well, it can be a bit more complex than that. What would this do? UPDATE mytable SET emptype = manager PRINT all done PRINT @@ROWCOUNT This returns 0. Why? The PRINT statement does not change any rows. As a matter of fact, PRINT statements never return rows. If you want to use a rowcount, you must use it immediately after the row from which you want to pull the data. The following code doesn t work the way you d expect, either: BEGIN TRANSACTION UPDATE mytable SET emptype = manager COMMIT TRANSACTION print @@ROWCOUNT The COMMIT TRANSACTION does not change or select any rows, so there s a zero value in @@ROWCOUNT. You ll learn more about BEGIN TRANSACTION and COMMIT TRANSACTION a little later in this chapter. Now imagine that you are working on finishing your great masterpiece batch, but you need to go home and get some sleep. How are you going to remember where you left off when you come back in the morning? COMMENTS Comments have two entirely different purposes within a T-SQL batch or stored procedure. First, they can be used to document code, to make it easier for folks who have to maintain software in the future. Second, they can be used to temporarily disable lines of code within your batch when you re trying to get it working.

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 343 If this value returns 128, then the flag was previously set, and ARITHIGNORE is turned on. If the value returns 0, then the flag was not set, and ARITHIGNORE is turned off. Other values that use this are the status value in the sysdatabases table and the status value in the sysobjects table. Using @@ROWCOUNT What s the fuss with @@ROWCOUNT? It just returns the number of rows affected by the previous statement. Well, it can be a bit more complex than that. What would this do? UPDATE mytable SET emptype = manager PRINT all done PRINT @@ROWCOUNT This returns 0. Why? The PRINT statement does not change any rows. As a matter of fact, PRINT statements never return rows. If you want to use a rowcount, you must use it immediately after the row from which you want to pull the data. The following code doesn t work the way you d expect, either: BEGIN TRANSACTION UPDATE mytable SET emptype = manager COMMIT TRANSACTION print @@ROWCOUNT The COMMIT TRANSACTION does not change or select any rows, so there s a zero value in @@ROWCOUNT. You ll learn more about BEGIN TRANSACTION and COMMIT TRANSACTION a little later in this chapter. Now imagine that you are working on finishing your great masterpiece batch, but you need to go home and get some sleep. How are you going to remember where you left off when you come back in the morning? COMMENTS Comments have two entirely different purposes within a T-SQL batch or stored procedure. First, they can be used to document code, to make it easier for folks who have to maintain software in the future. Second, they can be used to temporarily disable lines of code within your batch when you re trying to get it working.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

342 Part I EXAM PREPARATION TABLE 6.1 continued GLOBAL VARIABLES IN SQL SERVER 2000 Global Variable Function @@SERVICENAME Returns the name of the service under which SQL Server is running. @@SPID Returns the current process identifier used by SQL Server. @@TEXTSIZE The maximum number of bytes that will be returned in a result set to the current connection from selecting from a TEXT or IMAGE column. @@TIMETICKS The number of microseconds that occur in one tick of the computer s clock. @@TOTAL_ERRORS The total number of disk read/write errors that SQL Server has had since its last restart. @@TOTAL_READ The total number of physical disk reads that SQL Server has done since it was last started. @@TOTAL_WRITE The total number of physical disk writes that SQL Server has done since it was last started. @@TRANCOUNT This returns the number of transactions deep the current statement is in a nested transaction. @@VERSION Returns the version string (date, version, and processor type) for the SQL Server. User Options The @@OPTIONS variable uses a technique called bitmasking to return data. It s a good exercise in batch programming technique to see how to return specific data from the @@OPTIONS variable, because columns in some system tables use the same technique to store data. To retrieve data from a bitmasked value, you need to have the map for how the value is laid out. The complete map for the value returned by the @@OPTIONS variable is found in SQL Server blogs Online under the topic User Options Option. In the table within that topic, the user option for ARITHIGNORE is 128. So, to check whether the ARITHIGNORE flag is turned on, take the options variable and use the logical AND function to see whether the flag is set: DECLARE @UserOption int SET @UserOption = @@OPTIONS PRINT @UserOption & 128

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 341 Global Variable Function @@IDENTITY Returns the value used for the last INSERT INTO an identity column for the current connection. @@IDLE Returns the time in milliseconds that SQL Server has been idle since the last restart. @@IO_BUSY Returns the time in milliseconds that SQL Server has spent waiting for IO to return from a read or write request. @@LANGID Returns the language identifier of the current language in use. @@LANGUAGE Returns the name of the language currently in use, which is probably more useful than the ID number. @@LOCK_TIMEOUT Returns the number of milliseconds that the current connection will wait for a lock to clear to complete its work. @@MAX_CONNECTIONS The maximum number of simultaneous user connections allowed on the current SQL Server. @@MAX_PRECISION The precision used by the DECIMAL and NUMERIC data types on the server. By default, this is 38. @@NESTLEVEL The current nesting level during stored procedure execution. (See Chapter 9.) @@OPTIONS Returns an integer representing the settings of the user options for the current connection. (See User Options, later in this chapter.) @@PACK_RECEIVED The number of network packets received by the SQL Server since it was last restarted. @@PACK_SENT The number of packets sent by the SQL Server since it was last restarted. @@PACKET_ERRORS The number of packet errors that the SQL Server has seen since it was last restarted. @@PROCID The stored procedure identifier of the currently executing stored procedure. @@REMSERVER Returns the name of the SQL Server running the remote stored procedure. @@ROWCOUNT Returns the number of rows returned by the last statement. (See Using @@ROWCOUNT, later in this chapter.) @@SERVERNAME Returns the name of the current server. continues

For high quality java hosting services please check java web hosting website.