How to: Create Stored Procedures and User-Defined Functions

You can use Server Explorer to create stored procedures. Stored procedures can define complex business rules, control data modification, limit access through security permissions, provide transaction integrity, and generally do the database work your application requires.

With Microsoft SQL Server 2000 and later, you can also use Server Explorer to create user-defined functions. A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User-defined functions always return a value. Depending on the type of value it returns, each user-defined function falls into one of three categories:

• Scalar-valued function   A user-defined function can return a scalar value such as an integer or a timestamp. If a function returns a scalar value, you can use it in a query anywhere you would use a column name.

• Inline function   If a user-defined function contains a single SELECT statement and that statement is updatable, then the tabular result returned by the function is also updatable. Such functions are called inline functions. When an inline function returns a table, you can use that function in the FROM clause of another query. For more information, see How to: Create Queries using Something Besides a Table.

• Table-valued function   If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, then the tabular result returned by that function is not updatable. When a table-valued function returns a table, you can use that function in the FROM clause of another query.

Note

In the following examples, dbo is an acronym for database owner, and is used to qualify the stored procedure and user-defined function name. The dbo is a user that has implied permissions to perform all activities in the database. Any object created by any member of the sysadmin role belongs to dbo automatically. In the following examples, the dbo name qualifier is included.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To create a new stored procedure

1. In Server Explorer, right-click the Stored Procedures folder or any stored procedure in that folder.

2. Choose Add New Stored Procedure on the shortcut menu.

   A new stored procedure is created with the following skeleton SQL statements:

   CREATE PROCEDURE dbo.StoredProcedure1
   /*
      (
         @parameter1 datatype = default value,
         @parameter2 datatype OUTPUT
      )
   */
   AS
      /* SET NOCOUNT ON */
      RETURN
   

3. You can replace StoredProcedure1 in the first line with the name of the new procedure. For example, you might use MyProcedure as the name:

   CREATE PROCEDURE dbo.MyProcedure
   

   Note

   Stored procedures must have unique names. If you choose a name that is already assigned to another stored procedure, an error message warns you that a stored procedure with that name already exists.

4. Write the remaining procedure text in SQL.

   For more information and examples of stored procedures, see the documentation for your database server. If you are using Microsoft® SQL Server™, see "CREATE PROCEDURE" in SQL Server Books Online.

To create a new user-defined function

1. In Server Explorer, right-click the Functions folder or any function in that folder.

2. Point to Add New and then choose Inline Function, Table-valued Function, or Scalar-valued Function on the shortcut menu.

   Note

   You cannot modify the skeleton SQL statements for a new function into the SQL statements for a different function type and save the results. For example, if you begin with an inline function, you cannot modify the SQL statements to a scalar-valued function. The save process will fail.

   A new user-defined function is created using skeleton SQL statements. For example, choosing Scalar-valued Function displays the following skeleton SQL statements:

   CREATE FUNCTION dbo.Function1
      (
      /*
      @parameter1 datatype = default value,
      @parameter2 datatype
      */
      )
   RETURNS /* datatype */
   AS
      BEGIN
         /* sql statement ... */
      RETURN /* value */
      END
   

3. You can replace Function1 in the first line with the name of the new function. For example, you might use MyFunction as the name:

   CREATE FUNCTION dbo.MyFunction
   

   Note

   User-defined functions must have unique names. If you choose a name that is already assigned to another function, an error message warns you that a function with that name already exists.

4. Write the remaining function text in SQL.

Note

If the shortcut menu does not offer the options you need, the version of Visual Studio you are using may not support this feature. For more information see Visual Database Tools Editions.

For more information and examples of user-defined functions, see the documentation for your database server. If you are using Microsoft® SQL Server™, see "CREATE FUNCTION" in SQL Server Books Online.

See Also

Concepts

Expressions in Queries

Other Resources

Working with Stored Procedures and User-Defined Functions