--- title: "Creating, Altering, and Removing Stored Procedures | Microsoft Docs" ms.custom: "" ms.date: "06/13/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: ms.topic: "reference" helpviewer_keywords: - "stored procedures [SMO]" ms.assetid: 2a072f9c-8f11-4364-ab71-3990735a8d66 author: stevestein ms.author: sstein manager: craigg --- # Creating, Altering, and Removing Stored Procedures In [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] Management Objects (SMO), stored procedures are represented by the object. Creating a object in SMO requires setting the property to the [!INCLUDE[tsql](../../../includes/tsql-md.md)] script that defines the stored procedure. Parameters require the \@ prefix and must be created individually by using objects and adding to the collection of the object. ## Example To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see [Create a Visual Basic SMO Project in Visual Studio .NET](../../../database-engine/dev-guide/create-a-visual-basic-smo-project-in-visual-studio-net.md) or [Create a Visual C# SMO Project in Visual Studio .NET](../how-to-create-a-visual-csharp-smo-project-in-visual-studio-net.md). ## Creating, Altering, and Removing a Stored Procedure in Visual Basic This code example shows how to create a stored procedure for the [!INCLUDE[ssSampleDBnormal](../../../includes/sssampledbnormal-md.md)] database. The example returns the last name of an employee when it is given the employee ID number. The stored procedure requires one input parameter to specify the employee ID number and one output parameter to return the last name of the employee. ## Creating, Altering, and Removing a Stored Procedure in Visual C# This code example shows how to create a stored procedure for the [!INCLUDE[ssSampleDBnormal](../../../includes/sssampledbnormal-md.md)] database. The example returns the last name of an employee when it is given the employee ID number (`BusinessEntityID`). The stored procedure requires one input parameter to specify the employee ID number and one output parameter to return the last name of the employee. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Reference the AdventureWorks2012 database. Database db; db = srv.Databases["AdventureWorks2012"]; //Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor. StoredProcedure sp; sp = new StoredProcedure(db, "GetLastNameByBusinessEntityID"); //Set the TextMode property to false and then set the other object properties. sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; //Add two parameters. StoredProcedureParameter param; param = new StoredProcedureParameter(sp, "@empval", DataType.Int); sp.Parameters.Add(param); StoredProcedureParameter param2; param2 = new StoredProcedureParameter(sp, "@retval", DataType.NVarChar(50)); param2.IsOutputParameter = true; sp.Parameters.Add(param2); //Set the TextBody property to define the stored procedure. string stmt; stmt = " SELECT @retval = (SELECT LastName FROM Person.Person,HumanResources.Employee WHERE Person.Person.BusinessEntityID = HumanResources.Employee.BusinessentityID AND HumanResources.Employee.BusinessEntityID = @empval )"; sp.TextBody = stmt; //Create the stored procedure on the instance of SQL Server. sp.Create(); //Modify a property and run the Alter method to make the change on the instance of SQL Server. sp.QuotedIdentifierStatus = true; sp.Alter(); //Remove the stored procedure. sp.Drop(); } ``` ## Creating, Altering, and Removing a Stored Procedure in PowerShell This code example shows how to create a stored procedure for the [!INCLUDE[ssSampleDBnormal](../../../includes/sssampledbnormal-md.md)] database. The example returns the last name of an employee when it is given the employee ID number (`BusinessEntityID`). The stored procedure requires one input parameter to specify the employee ID number and one output parameter to return the last name of the employee. ```powershell # Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012 CD \sql\localhost\default\databases $db = Get-Item Adventureworks2012 # Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor. $sp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure -argumentlist $db, "GetLastNameByBusinessEntityID" #Set the TextMode property to false and then set the other object properties. $sp.TextMode = $false $sp.AnsiNullsStatus = $false $sp.QuotedIdentifierStatus = $false # Add two parameters $type = [Microsoft.SqlServer.Management.SMO.Datatype]::Int $param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter -argumentlist $sp,"@empval",$type $sp.Parameters.Add($param) $type = [Microsoft.SqlServer.Management.SMO.DataType]::NVarChar(50) $param2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter -argumentlist $sp,"@retval",$type $param2.IsOutputParameter = $true $sp.Parameters.Add($param2) #Set the TextBody property to define the stored procedure. $sp.TextBody = " SELECT @retval = (SELECT LastName FROM Person.Person,HumanResources.Employee WHERE Person.Person.BusinessEntityID = HumanResources.Employee.BusinessentityID AND HumanResources.Employee.BusinessEntityID = @empval )" # Create the stored procedure on the instance of SQL Server. $sp.Create() # Modify a property and run the Alter method to make the change on the instance of SQL Server. $sp.QuotedIdentifierStatus = $true $sp.Alter() #Remove the stored procedure. $sp.Drop() ``` ## See Also