Create a sample stored procedure

Use the following procedure to create a sample stored procedure in SSMS that returns AF element Name and Path when it has been passed an AF element name.

1. Start SSMS and connect to the database engine.

2. In the Object Explorer pane, navigate to Databases > Test Database > Programmability > Stored Procedures.

3. Right-click on Stored Procedure and select New > Stored Procedure.

   A new query window opens that contains a sample stored procedure.

4. Copy and paste the following commands into the query window.

   CREATE PROCEDURE GetAFElement
   
   -- Add the parameters for the stored procedure here
   
   @ElementName nvarchar(50) = 'B-210'
   AS
   
   BEGIN
   
   -- SET NOCOUNT ON added to prevent extra result sets from
   
   -- interfering with SELECT statements.
   
   SET NOCOUNT ON;
   
   -- Insert statements for procedure here
   
   SELECT afelement.Name, afelement.Path
   
   FROM MY_LINKED_AFSERVER.Master.Element.ElementHierarchy AS afelement
   
   WHERE afelement.name = @ElementName
   END
   

   Where MY_LINKEDSERVER is the name of the linked server being created. As written, the sample stored procedure passes the AF element name B-210.

   Note: When calling SQL Server stored procedures using named parameters with OLE DB, the parameter names must start with the '@' character. This is a SQL Server specific restriction.