About SQL Server Books Online and Transact-SQL reference informationAbout SQL Server Books Online and Transact-SQL reference information
Note The hyperlink in this topic goes to the Web. You can switch back to Help at any time.
Microsoft Access documents in its Help system and in The Microsoft Office XP Developer's Guide how to use and program Microsoft Access project that is connected to a Microsoft SQL Server database. However, you may also need additional information about SQL Server to answer specific product questions you might have, such as reference information on Transact-SQL or the SQL Server Enterprise Manager.
The primary source of information about Microsoft SQL Server is SQL Server Books Online. If you have installed Microsoft Office XP Developer or you have access to SQL Server 2000 on either a local or remote computer, then you have access to SQL Server Books Online through the Start menu. However, if you don't have access to SQL Server Books Online, you can download it from the SQL Server Books Online download Web page.
SQL Server documentation contains the following information.
Item Description SQL Server Books Online Contains comprehensive documentation about SQL Server 2000 including:
- Conceptual information.
- General instructions.
- Reference information about Transact-SQL syntax.
- Reference information about SQL Server error messages, numbers, and recovery actions.
- General programming references.
Enterprise Manager Help Help information about administering an SQL Server database. Replication Wizard Help information about configuring and managing replication. Client Configuration Utility Help Help information about managing SQL Server network connectivity options. ODBC Driver Help Help information about creating SQL Server ODBC data source definitions. Profiler Help Help information about capturing SQL Server events in a trace file for analysis and replay. Query Analyzer Help Help information about a graphical user interface that is used to interactively design and test Transact-SQL statements, batches, and scripts. Data Transformation Services (DTS) Designer Help Help information about importing, exporting, and transforming a variety of data sources. SQL Distributed Management Objects (SQL-DMO) Context-sensitive help information that is available from the development environment for SQL-DMO objects, properties and methods. The Pubs Sample Database The traditional SQL Server sample database. The Northwind SQL Sample Database A sample database similar to the Access Northwind sample database with specific SQL Server examples.
About stored procedures
Stored procedures can make managing your database and displaying information about that database and its users much easier. Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures are stored within a database; can be executed with one call from an application; and allow user-declared variables, conditional execution, and other powerful programming features.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.
You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:
- You can execute a series of SQL statements in a single stored procedure.
- You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.
- The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.
A single statement stored procedure contains only one SQL SELECT statement. You can graphically create or edit it in the Query Designer, and you can define extended properties (except column properties).
A multistatement stored procedure contains more than one SQL statement. You can create or edit it in the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a multistatement stored procedure by using the Query Builder. However, you cannot define extended properties.
Create a stored procedure
- In the Database window, click Queries
under Objects, and then click New.
- In the New Query dialog box, select Design Stored Procedure.
- Design the stored procedure in the Diagram and Grid panes of the Query Designer.
- Click Save
on the toolbar.
- In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.
Microsoft Access adds the stored procedure to Queries
- In the Database window, click Queries
- In the New Query dialog box, select Create Text Stored Procedure.
Access displays the SQL Text Editor with the following skeleton SQL syntax.
CREATE PROCEDURE "StoredProcedure1" /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ RETURN- Write the stored procedure by using Transact-SQL. For more information on Transact-SQL, see the SQL Server documentation.
Notes
If you're creating a stored procedure that returns rows from the database, the first statement after the CREATE PROCEDURE statement must be a SELECT statement, or you must use SET NOCOUNT ON at the top of the procedure. This ensures that the result set from the first SELECT statement is displayed; otherwise, no rows will display. For more information on the SET NOCOUNT ON SQL statement, see the SQL Server documentation.
You can graphically edit a SQL SELECT statement in a multistatement stored procedure by using the Query Builder.