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.
Input name of the trigger and type a script that will represent the trigger.
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.
Right-click anywhere in the SQL document, and then click Insert Snippet. The snippets list will appear.
The following code will be inserted to the document.
To edit a stored procedure
Right-click a procedure and select Edit Procedure from shortcut menu.
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 Data view of the stored procedure editor.
Click Stop Execution.
Right-click a stored procedure in Database Explorer and select Delete.