Working with Stored Procedures

Stored procedure (function) is a set of statements that resides on the server side and can be executed. With dbForge Fusion for SQL Server, you can create, edit, execute, and delete stored procedures.

Creating a Stored Procedure

1. In Database Explorer, navigate to the Procedures node.

2. Select New Procedure on the shortcut menu.

3. Input name of the trigger and type a script that will represent the trigger.

4. Save the document by clicking the Update Database button at 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

Creating a Stored Procedure with the Help of Snippet

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

1. Click the New SQL button on the Devart - Main toolbar.

2. Right-click anywhere in the SQL document, and then click Insert Snippet. The snippets list will appear.

3. Double-click the CreateProcedure snippet.

4. The following code will be inserted to the document.

   

5. Edit the code accordingly and save the trigger. If there are any errors in the SQL syntax, you will be notified about it.

Editing a Stored Procedure

To edit a stored procedure:

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

• or -

double-click a procedure.

Executing 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 the Data view of the stored procedure editor.

Stopping a Stored Procedure

Click Stop Execution.

Deleting a Stored Procedure

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

• Previous
• Next