SQL Server 2000 Stored Procedure & XML Programming in Visual Studio .NET

Drawer Data Matrix in Visual Studio .NET SQL Server 2000 Stored Procedure & XML Programming

SQL Server 2000 Stored Procedure & XML Programming
ECC200 Reader In VS .NET
Using Barcode Control SDK for Visual Studio .NET Control to generate, create, read, scan barcode image in .NET applications.
Data Matrix Generator In VS .NET
Using Barcode creator for .NET framework Control to generate, create ECC200 image in Visual Studio .NET applications.
to use the Alter Procedure statement to modify an existing stored procedure without affecting permissions and other dependent objects:
Scanning ECC200 In .NET
Using Barcode recognizer for VS .NET Control to read, scan read, scan image in Visual Studio .NET applications.
Bar Code Maker In .NET
Using Barcode generator for VS .NET Control to generate, create bar code image in Visual Studio .NET applications.
ALTER PROCEDURE prHelloWorld_1 AS SELECT 'Hello World again!' SELECT * from Inventory RETURN 0 GO
Bar Code Recognizer In VS .NET
Using Barcode decoder for .NET Control to read, scan read, scan image in VS .NET applications.
ECC200 Encoder In Visual C#.NET
Using Barcode creation for VS .NET Control to generate, create ECC200 image in Visual Studio .NET applications.
You may have noticed the Go command in the previous two examples. This command is not a SQL statement. It is not even part of the T-SQL language. It is a signal to Query Analyzer (and some other tools, such as isql and osql) to treat the SQL statements as one set a batch. All statements in a batch are compiled and executed together. In SQL Server 2000, it is possible to use the Object Browser to edit stored procedures:
Create Data Matrix 2d Barcode In .NET
Using Barcode generator for ASP.NET Control to generate, create Data Matrix image in ASP.NET applications.
ECC200 Drawer In Visual Basic .NET
Using Barcode generation for Visual Studio .NET Control to generate, create Data Matrix image in .NET applications.
1. If the Object Browser is not already present on the screen, select Tools | Object
Encoding UPC - 13 In Visual Studio .NET
Using Barcode encoder for VS .NET Control to generate, create EAN-13 image in .NET framework applications.
Encode DataMatrix In Visual Studio .NET
Using Barcode creator for .NET Control to generate, create ECC200 image in Visual Studio .NET applications.
Browser to display it.
Create ANSI/AIM Code 128 In VS .NET
Using Barcode generator for Visual Studio .NET Control to generate, create Code 128C image in Visual Studio .NET applications.
Making Code11 In .NET
Using Barcode printer for Visual Studio .NET Control to generate, create Code11 image in Visual Studio .NET applications.
2. Open the Asset database and then its list of stored procedures. 3. Find and right-click prHelloWorld_1 in the list and select Edit. Query Analyzer
Bar Code Decoder In .NET
Using Barcode scanner for .NET framework Control to read, scan read, scan image in .NET applications.
Printing Universal Product Code Version A In None
Using Barcode generation for Office Word Control to generate, create Universal Product Code version A image in Microsoft Word applications.
displays a Query window with the code of the stored procedure in it (see Figure 2-16).
Barcode Drawer In None
Using Barcode generator for Font Control to generate, create barcode image in Font applications.
Drawing Bar Code In None
Using Barcode creation for Microsoft Excel Control to generate, create bar code image in Office Excel applications.
Figure 2-16
Barcode Drawer In VS .NET
Using Barcode creator for ASP.NET Control to generate, create barcode image in ASP.NET applications.
Recognizing Bar Code In C#
Using Barcode Control SDK for .NET Control to generate, create, read, scan barcode image in .NET applications.
The Query window displays the stored procedure s code.
USS Code 128 Drawer In Java
Using Barcode creator for Java Control to generate, create Code128 image in Java applications.
Bar Code Creation In Visual Studio .NET
Using Barcode generation for Reporting Service Control to generate, create barcode image in Reporting Service applications.
2: The SQL Server Environment
NOTE
Do not be confused by the additional Set Quoted_Identifier and Set Ansi_Nulls statements. They are present just to set an optimal environment for the execution of the Alter Procedure statement. When they are present, the client session settings are ignored during the stored procedure execution.
4. Once you are satisfied with changes in the code, you can simply execute it
(Query | Execute).
Syntax Errors
Sooner or later you will make a typo, and the server will react with an error. Let s deliberately cause a problem to see how the server reacts.
1. Verify that you are in Query Analyzer and that Asset is your current database.
We will attempt to alter the code of prHelloWorld_1.
NOTE
There are two ways to type comments in the Transact-SQL language. If you type two dashes (- -), the rest of that line will be ignored by the server. Code stretched over multiple lines can be commented out by using /* and */ as delimiters at either end of the comment.
2. We will comment out the second line (the keyword As):
Alter Procedure prHelloWorld_1 --As Select 'Hello World again!' Select * from Inventory Return 0 Go
3. As soon as you execute this code, the server reports an error (see Figure 2-17).
Keep in mind that SQL Server is not a perfect compiler. Some error messages that it reports may not contain sufficient details or may even be misleading. The rule of thumb is simple: check your basic syntax first.
If you double-click the error message in the Results pane, Query Analyzer will try to return the cursor to the line containing the error in the Query pane (actually, to the first line that appears after the last statement that executed correctly). This is very useful when you are executing a long batch.
SQL Server 2000 Stored Procedure & XML Programming
Figure 2-17
An error in Query Analyzer
Another advantage the Alter statement has over the Drop/Create approach is that the stored procedure remains intact after an unsuccessful attempt such as we produced in this example. You have made your first steps in the development of stored procedures in Transact-SQL. The next chapter explores SQL Server stored procedure design in greater detail.
Naming Conventions
One of the most important things you can do to improve the quality and readability of your code is to use standards to name variables, procedures, and objects in your database. We will now go though the importance of using naming conventions and describe one used in this book.
2: The SQL Server Environment
Why Bother
Unfortunately, many developers dislike, and avoid using, standards. Their usual explanation is that standards stifle their creativity, or that the constant need to comply with standards distracts them from what they are really being paid to do. While there may be some truth in these claims, compliance with reasonable standards is another one of those habits that differentiates the professional from the amateur (not to mention the prima donna). Often, however, the problem lies not in the presence or content of a standard but in the spirit of its enforcement. Frequently, organizations (or the people in them) get carried away. They forget the reasons for enforcing standards, and the standards become an end in themselves. There are several valid reasons for introducing naming conventions: The main reason for the existence of naming conventions is to make code readable, understandable, and easy to remember. A standard allows developers to speak a common language that will help the team to communicate more efficiently. Team members will be able to understand and learn parts of the code with which they are not familiar. New team members will have to learn only one standard way of coding instead of having to learn the distinct coding habits of individual team members. Time will be saved and confusion avoided, since it will be easier to define and identify unique names for objects and variables. If you are developing a project on your own, you might go through it without implementing a standard (or without being aware that you actually have a standard). However, in most cases, the introduction of a standard becomes critical, such as when the following conditions exist: More than one developer is working on the project. The code or project will be maintained or reviewed by other developers who are not currently members of the team. The application under development is too complex for one person to analyze all aspects at once and requires different components to be designed and implemented separately.
Copyright © OnBarcode.com . All rights reserved.