How to work with stored procedures

Last modified: October 1, 2024

Stored procedure (function) is a set of statements that resides on server side and can be executed. Using dbForge Studio, you can create, edit, execute and delete stored procedures.

Create a stored procedure

In Database Explorer, navigate to the Procedures node.
Select New Procedure from the shortcut menu.
Input name of the trigger and type a script that will represent the trigger.
Save the document by clicking the Update Database button on the bottom of the window, or the Save button on the Standard toolbar. If there are any errors in the SQL syntax, you will be notified about it.

CREATE PROCEDURE syntax

CREATE PROCEDURE dbo.uspGetManagerEmployees
    @BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;
    -- Use recursive query to list out all Employees required for a particular Manager
 WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
 AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees
 for Manager n
 FROM [HumanResources].[Employee] e
                 INNER JOIN [Person].[Person] p 
                 ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
  SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
                 INNER JOIN [Person].[Person] p 
                 ON p.[BusinessEntityID] = e.[BusinessEntityID

Create a stored procedure with the help of snippets

To simplify the process of a Stored Procedure creation, you may use the CreateProcedure snippet.

Click the New SQL button on the Standard toolbar.
Right-click anywhere in the SQL document, and then click Insert Snippet. The snippets list will appear.
Double-click the CreateProcedure snippet.
The following code will be inserted to the document.
Edit the code accordingly and save the trigger. If there are any errors in the SQL syntax, you will be notified about it.

Edit a stored procedure

To edit a stored procedure

In Database Explorer, navigate to the Procedures node and expand it.
Right-click a procedure and select Edit Procedure from shortcut menu.

or

double-click a procedure.

Execute a stored procedure

Right-click a stored procedure in Database Explorer and select Execute.

The stored procedure editor opens if it was not opened before. If the procedure accepts parameters, you will see the parameters dialog where you can assign initial values for procedure arguments.

Once the stored procedure is executed, you will see a corresponding record in the Output window. The data returned by the stored procedure will appear in Data view of the stored procedure editor.

Editing Parameters