Understand how to create a user-defined stored procedure.
Creating Stored Procedures in SQL-Server
You can use Enterprise Manager in SQL Server 2019 to create a user-defined stored procedure. Here are the steps on how to do it:
Open Enterprise Manager and connect to your SQL Server instance.
Expand the instance, then expand Databases, and then expand the database that you want to create the stored procedure in.
Expand Programmability, and then right-click Stored Procedures and select New Stored Procedure.
In the New Stored Procedure dialog box, enter a name for the stored procedure, and then select the database that you want to create the stored procedure in.
In the Query pane, enter the Transact-SQL code for the stored procedure.
Click the Parse button to check the syntax of the stored procedure.
If there are no errors, click the Execute button to create the stored procedure.
The stored procedure will be created in the database and you will be able to see it in the Stored Procedures folder in Object Explorer.
Here is an example of a stored procedure that you can create:
CREATE PROCEDURE uspGetEmployees
AS
BEGIN
SELECT * FROM Employees
END
This stored procedure will return all of the employees from the Employees table. To execute the stored procedure, you can use the following Transact-SQL code:
EXEC uspGetEmployees
This will return all of the employees from the Employees table.
You can use either Enterprise Manager or Transact-SQL to create a stored procedure.
Using Enterprise Manager
To create a stored procedure with Enterprise Manager:
Drill down to the Stored Procedures folder.
Right-click the Stored Procedures folder.
Choose the New Stored Procedure menu option.
Enter the appropriate Transact-SQL statements in the Stored Procedure Properties dialog box, shown below:
Creating a new stored procedure
As with triggers, you will need to use Transact-SQL within Enterprise Manager to create and modify stored procedures.
Using Transact-SQL
To create a stored procedure using Transact-SQL, you will use the CREATE PROCEDURE statement, which uses the syntax shown in the following series of images
1) procedure_name is the name of the stored procedure you are going to create
2) number is the procedure number if you are using multiple procedures with the same name. The main advantage of the seldom used option is that it groups stored procedures so that they can be deleted at the same time.
3) @parameter is the name of the argument that is used to pass values into, and possibly out of the stored procedure and can only be used when using a cursor variable.
4) @data_type is the parameter's datatype. Every parameter must have a datatype.
5) default is a placeholder for a value that is to be assigned to the parameter as a default. A Default is a value that is used if no explicit value is supplied.
6) OUTPUT indicates that the stored procedure will pass this value back to a calling program.
7) WITH RECOMPILE indicates that SQL Server will recompile the query plan every time it is executed, as opposed to reusing it from cache. This option is seldom used because caching the query plan is one of the main advantages of using stored procedures. The advantage to this option is that it guarantees that the query plan that is used will not be outdated. You cannot use this option with the FOR REPLICATION option.
8) WITH ENCRYPTION encrypts the text of the stored procedure so that it cannot be read from the syscomments table
9) WITH RECOMPILE, ENCRYPTION specifies both the WITH RECOMPILE and WITH ENCRYPTION options.
10) FOR REPLICATION indicates that this stored procedure will be used only by replication subscribers and not by other processes in the database.
11) As sql_code specifies that Transact-SQL code will be used to define your stored procedure. Multiple Transact-SQL statements are often used in the definition of a stored procedure.
In the next lesson, you will practice creating a user-defined stored procedure.
