Basic Operations in Management Studio

This section will serve as a primer to introduce you to the concepts of executing, creating, and editing stored procedures. I will walk through the usage of the most important SQL Server client tools. Since Transact-SQL is just another programming language, I will follow a tradition first established by an unknown programmer and start with a trivial Hello World example. Finally, I will show you how to view and edit rows in a table and how to create and modify table structure.

What Are Stored Procedures?

Stored procedures are database objects that encapsulate collections of Transact-SQL statements on the server for later repetitive use. Although stored procedures use nonprocedural Transact-SQL statements, they are in essence procedural. They define algorithms that determine how operations should be performed.

Stored procedures are the Transact-SQL equivalents of subroutines in other programming languages. Developers of custom database applications can use all major programming constructs while building stored procedures:

• Variables

• Data types

• Input/output parameters

• Return values

• Conditional execution

• Loops

• Comments

SQL Server includes a set of system stored procedures designed for administering the system. Their role is to provide information, set configuration, control the environment, manage user-defined objects, and schedule and run custom tasks.

Execution of Stored Procedures in Management Studio

The execution of stored procedures in Management Studio is very simple. Let's try it using the system stored procedure sp_who, which lists all users and processes connected to the system.

1. Run Management Studio (Start | Programs | Microsoft SQL Server 2005 | SQL Server Management Studio).

2. The Management Studio application prompts you for a server, username, and password, as shown on Figure 1-10. If the application is unable to connect to the server, you should check whether the SQL Server service is running and whether you correctly typed the name of the server and/or your username and password.

   
   Figure 1-10: Connect to Server dialog box

   Tip 

   If you are a SQL Server or local administrator, you can use Windows Authentication. SQL Server will use your Windows login to authorize your access to database and server objects. If you have set SQL Server to work in mixed mode during installation and you know the sa (system administrator) login and password, you can switch to SQL Server Authentication. The name of your machine is the name of the SQL Server. If you are working on the machine that has SQL Server installed, you can always use "(local)" or a simple dot "." to refer to the current machine as the server to which you want to connect. If you have created a named instance of SQL Server, you will have to reference it after a backslash "\" character.

3. Once you have logged in successfully, Management Studio displays a tree of server objects in the Object Browser.

4. Open the Query window by clicking New Query | New SQL Server Query. You are prompted to confirm connection parameters.

5. In the Query pane, type the following code:

         exec sp_who 

   Note 

   Management Studio uses different colors to distinguish keywords, variables, comments, and constants.

6. To run the stored procedure, you can select Query | Execute, click the Execute button on the toolbar, or press CTRL-E. The application will split the screen to display both query and results (see Figure 1-11). This stored procedure lists active processes on the current server as well as the login names of the users who started them.

   
   Figure 1-11: Executing a stored procedure in Management Studio

7. You can click the Messages tab to see whether SQL Server has returned any messages along with the result (such as the number of records, a warning, or an error).

8. Select Query | Results To | Results to Text and then execute the query again (Query | Execute). The window displays the result set in the form of text. Messages are mixed with result sets in this case.

   Note 

   Before we continue, please ensure that you have installed the sample Asset5 database. If you have not already installed it, review the download and installation instructions in the Introduction.

Editing Stored Procedures

I will now lead you through the process of editing stored procedures.

1. Open the Object Browser.

2. Expand Databases | Asset5 | Programmability | Stored Procedures and the program will display a list of stored procedures.

3. In this list, find a stored procedure named ap_Equipment_List, and right-click it. When you right-click an object, a context-sensitive menu appears with options to let you perform operations such as deleting and renaming the stored procedure or creating a new stored procedure.

4. Select Modify on the pop-up menu. The application opens a window to allow you to view and edit the stored procedure (see Figure 1-12).

   
   Figure 1-12: A window for modifying a stored procedure

Creating Stored Procedures

To create a stored procedure, follow these steps:

1. Right-click any stored procedure or the Stored Procedures node in the Object Browser and select New Stored Procedure from the pop-up menu. Management Studio displays a window with a template for the stored procedure (see Figure 1-13).

   
   Figure 1-13: Creating new stored procedures

2. Replace the existing Select statement with the following code after the As keyword:

         Select 'Hello world'      Select * from dbo.EqType 

3. Set the name of the procedure to ap_Hello and drop placeholders for parameters (between Create Procedure and As). Alternatively, you can delete everything in the Query window and simply type the following:

         Create Procedure ap_Hello      as         Select 'Hello world'         Select * from dbo.EqType      return      GO 

4. Click Query | Parse on the main menu to verify the syntax of the procedure.

5. Click OK.

6. Select Query | Execute from the menu. The procedure is compiled and stored in the database. Unfortunately, you will not be able to see it in the list of stored procedures in the Object Browser. You have to refresh it by right-clicking the Stored Procedure node and selecting Refresh from the context menu.

I will now show you how to execute this stored procedure without typing code:

1. Display the context-sensitive menu for the procedure (right-click it in the Object Browser).

2. Choose Script Stored Procedure As | Execute To | New Query Editor Window. Management Studio generates code for executing the stored procedure.

3. Choose Query | Execute (or press CTRL-E). Management Studio returns two result sets in this case, since we have two Select statements in our stored procedure (see Figure 1-14).

   
   Figure 1-14: Execution of stored procedure with two result sets

Editing Stored Procedures in the Query Window

Before Enterprise Manager and Management Studio, administrators used scripts to manage database objects. Traditionally, DBAs included the code for deleting (dropping) the original stored procedure and then re-creating the stored procedure (with the changed code):

1. Open a new Query window (click New Query | New SQL Server Query).

2. Confirm connection parameters.

3. Type the following code in the Query pane:

         DROP PROCEDURE ap_Hello      GO      CREATE PROCEDURE ap_Hello      AS         SELECT 'Hello World'         SELECT * from dbo.Inventory      return      GO 

4. If you haven't changed the default settings for your login, Management Studio opens a Query window against the master database. Use the Available Database drop-down list box in the toolbar to switch to the Asset5 database (see Figure 1-15). Alternatively, you can add the following code at the top of the query:

         USE Asset5      GO 

   
   Figure 1-15: Available databases

5. Execute the code by selecting Query | Execute (CTRL-E).

   Note 

   Alternatively, you could have generated this code using Script Stored Procedure As | Drop To | New Query Window and Script Stored Procedure As | Create To | Clipboard.

SQL Server first deletes the existing stored procedure and then re-creates it (with the new code). The trouble with this method (dropping and then re-creating) is that you also drop some attributes associated with the stored procedure (such as permissions), which also affects other dependent objects. Since Microsoft SQL Server 7.0, it has been possible to use the Alter Procedure statement to modify an existing stored procedure without affecting permissions and other dependent objects:

      ALTER PROCEDURE ap_Hello      AS         SELECT 'Hello World again!'         SELECT * from dbo.Inventory      return      GO 

Note 

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 Transact-SQL language. It is a signal to the Query window in Management Studio (and to some other tools, such as SQLCMD) to treat the SQL statements as one set—a hatch. All statements in a hatch are compiled and executed together.

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. We will create an error by commenting out a required part of the code.

Note 

There are two ways to type comments in the Transact-SQL language. If you type two dashes (– –), the rest of that line will he ignored by the server. Code stretched over multiple lines can he commented out by using /*and /ast;as delimiters at either end of the comment.

1. Right-click Procedure_1 and choose Script Stored Procedure As | Alter | New Query Window.

2. Comment out the second line (the keyword As):

         Alter Procedure Procedure_l      --As         Select 'Hello World again!'         Select * from dbo.Inventory      return      Go 

3. As soon as you execute this code, the server reports an error (see Figure 1-16). 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.

   
   Figure 1-16: Example of syntax error

Tip 

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 feature is very useful when you are executing a long batch.

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.

Viewing and Editing Tables

I will now demonstrate how to view, edit, and change tables in Management Studio.

1. Open Management Studio.

2. Expand Databases | Assets | Tables.

3. Find the dbo.EqType table in the list and right-click it to open its context-sensitive menu.

4. Select Open Table and the program will open its records in a grid (see Figure 1-17).

   
   Figure 1-17: Viewing and rows in the Table window

You can now use the buttons at the bottom of the grid or the cursor keys to navigate. The record that contains the cursor (that is, the current record) has a little arrow at the left border of the grid. To add records to the table, move the cursor below the last row of the grid (or press the Move To New Row button). If you want to modify one of the existing records, position your cursor on it and press the F2 key. To delete a record, you can click its left border to display a context-sensitive menu and then select the Delete command.

Tip 

Naturally, you can view tables and perform table modifications using SQL statements (Select, Insert, Update, and Delete) from the Query window. In fact, that is the preferred way.

The Table window, shown in Figure 1-18, is in fact a special form of the Query window. Its context-sensitive menu contains a Pane submenu that contains Diagram, Criteria, SQL, and Results options. They work as switches to open panes for managing the query. The Diagram pane provides a visual means of managing tables that participate in the query and their links. The Criteria pane allows you to specify columns in the result set and criteria for selecting records. The SQL pane displays the SQL query statement that corresponds to the settings in the Diagram and Criteria panes. If you edit the content of the SQL pane, Management Studio will update the Diagram and Criteria panes and vice versa. The Results pane shows the result of the query specified in the SQL pane or the Diagram and Criteria panes. You can also edit rows in this pane if your query is simple enough (based on a single table). You can issue statements other than Select in this window. You can use Change Type to switch to the Insert, Update, or Delete statements. The window is similar to the Query Design window in Microsoft Access and Visual Studio, so I will not spend any more time describing it here.

Figure 1-18: A Query window for editing tables

Changing Table Structure

I will now show you how to modify a table's structure:

1. Locate a table in the Object Browser and right-click it.

2. When you select Modify from the context-sensitive menu, the program will open a new window to display its structure (see Figure 1-19). The top half of the window is a grid that you can use to add and modify table columns. The bottom half of the window shows the properties of the column that is selected in the top grid.

   
   Figure 1-19: Changing a table structure in Management Studio

You can add a new table in the same manner. Right-click in an existing table or on the Tables node in the Object Browser, and then select New Table from the pop-up menu.

Now you have made your first steps in the development of stored procedures and managing tables in Transact-SQL. The next chapter explores SQL Server stored procedure design in greater detail.