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.
In Database Explorer, navigate to the Procedures node.
Select New Procedure on 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 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 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
To simplify the process of a Stored Procedure creation, you may use the CreateProcedure snippet.
Click the New SQL button on the Devart - Main 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.
To edit a stored procedure:
double-click a 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.
Click Stop Execution.
Right-click a stored procedure in Database Explorer and select Delete.