SQL Create Stored Procedure

SQL stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have a query that you write over and over again, save it as a stored procedure, and then just call it to execute it in SQL. We can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter values that are passed.
SQL stored procedure creates a Common Language Runtime (CLR) stored procedure in SQL Server, Azure SQL database. Stored procedures are similar to procedures in other programming languages in that they can follow:
Use the following statement to create a permanent procedure in the current database or a temporary procedure in the sample database.
Syntax
  1. CREATE PROCEDURE procedure_name
  2. AS
  3. sql_statement
  4. GO;

    To execute a Stored Procedure

    Syntax
    1. EXEC procedure_name;

    Creating a simple Stored_ Procedure

    The following example creates a stored procedure that returns all OrderDetails (OrderName,OrderAddress, and OrderDate supplied), their name, Address, Date, and names from a view in the Sample database, this procedure does not use any parameters.
    The following example demonstrates three methods of executing the procedure.
    Syntax
    1. CREATE PROCEDURE sp_GetAllOrderDetails
    2. AS
    3. SET NOCOUNT ON;
    4. SELECT OrderName, orderAddress, OrderDate
    5. FROM OrderDetails
    6. GO
    7. SELECT * FROM OrderDetails
      Example
      The Sp_GetOrderDetail procedure can be executed in the following ways
      Syntax
      1. EXECUTE Sp_GetOrderDetail ;
      2. GO
      3. -- Or
      4. EXEC Sp_GetOrderDetail ;
      5. GO
      6. -- Or, if this procedure is the first statement within a batch:
      7. Sp_GetOrderDetail

        Returning more than one result set in Stored Procedure

        The following procedure returns two result sets in sp.
        Syntax
        1. CREATE PROCEDURE SP_multipleOrderDetails
        2. AS
        3. SELECT TOP(10) OrderId, OrderName, orderAddress, OrderDate FROM OrderDetails
        4. SELECT TOP(10) EmpId, EmpAddress, EmpCity FROM EmployeeDetail ;
        5. GO

          Creating a CLR procedure

          This example creates the sp_GetNewPhotoDetails procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

          Syntax
          1. CREATE ASSEMBLY HandlingLOBUsingCLR
          2. FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
          3. GO
          4. CREATE PROCEDURE sp_GetNewPhotoDetails
          5. (
          6. @NewPhotoID int,
          7. @CurrentDirectory nvarchar(1024),
          8. @FileName nvarchar(1024)
          9. )
          10. AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
          11. GO

            Passing Parameters

            This section demonstrates how to use input and output parameters to pass values to and from a stored procedure in SQL.

            Creating a procedure with input parameters

            The following example creates a stored procedure that returns information for a specific EmployeeDetails by passing values for the employee's Empname and EmpAddress. This procedure accepts only exact matches for the parameters passed.
            Syntax
            1. IF OBJECT_ID ( 'Sp_GetEmployeeDetails', 'P' ) IS NOT NULL
            2. DROP PROCEDURE Sp_GetEmployeeDetails
            3. GO
            4. CREATE PROCEDURE Sp_GetEmployeeDetails
            5. @EmpName nvarchar(50),
            6. @EmpAddress nvarchar(50)
            7. AS
            9. SET NOCOUNT ON;
            10. SELECT EmpId, EmpName,EmpAddress,EmpCity
            11. FROM EmployeeDetail
            12. WHERE EmpName = @EmpName AND EmpAddress= @EmpAddress ;
            13. GO

              The Sp_GetEmployeeDetailsprocedure can be executed in the following ways.
              Syntax
              1. EXECUTE Sp_GetEmployeeDetail N'Rohit', N'Noda';
              2. -- Or
              3. EXEC sp_GetEmployeeDetail @EmpName = N'Rohit', @EmpAddress = N'Noida';
              4. GO
              5. -- Or
              6. EXECUTE sp_GetEmployeeDetail @EmpName = N'Rohit', @EmpAddress = N'Noida';
              7. GO
              8. -- Or, if this procedure is the first statement within a batch:
              9. sp_GetEmployeeDetail N'Rohit', N'Noida';

              Using a procedure with wildcard parameters in SQL

              The following example creates a stored procedure that returns information for employees by passing full or partial values for the OrderDetails OrderName and OrderDetail. This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter N).
              Syntax
              1. IF OBJECT_ID ( 'OrderDetails', 'P' ) IS NOT NULL
              2. DROP PROCEDURE Sp_OrderDetails2 ;
              3. GO
              4. CREATE PROCEDURE sp_OrderDetail2
              5. @OrderName nvarchar(50) = N'A%',
              6. @OrderAddress nvarchar(50) = N'%'
              7. AS
              8. SET NOCOUNT ON;
              9. SELECT OrderName, OrderAddress
              10. FROM OrderDetails
              11. WHERE OrderName LIKE OrderName AND orderAddress LIKE @OrderAddress;
              The Sp_OrderDetails2 procedure can be executed in many combinations. Only a few possible combinations are shown here.
              1. EXECUTE Sp_OrderDetails2;
              2. -- Or
              3. EXECUTE Sp_OrderDetails N'Wi%';
              4. -- Or
              5. EXECUTE Sp_OrderDetails @OrderName = N'%';
              6. -- Or
              7. EXECUTE Sp_OrderDetails N'[AP]ars[MN]n';
              8. -- Or
              9. EXECUTE Sp_OrderDetails N'AP', N'MN';
              10. -- Or
              11. EXECUTE Sp_OrderDetails N'A%', N'M%';

                Using output parameters in stored_porcedure

                The following example creates the Get_OrderDetails procedure. This storedprocedure shows using multiple Select statements and multiple output parameters. Output parameters enable an external procedure, a batch, or more than one SQL statement to access a value set during the procedure execution.
                Syntax
                1. IF OBJECT_ID ( 'Get_OrderDetails', 'P' ) IS NOT NULL
                2. DROP PROCEDURE Production.uspGetList;
                3. GO
                4. CREATE PROCEDURE GetUserDetails
                5. @OrderId varchar(50),
                6. @OrderName varchar(50),
                7. @OrderAddress varchar(50),
                8. @OrderDate datetime
                9. AS
                10. SET NOCOUNT ON;
                11. SELECT p.[EmpName] AS Product, p.EmpName AS 'Noida'
                12. FROM EmployeeDetail AS p
                13. JOIN OrderDetails AS s
                14. ON OrderId = EmpId
                15. WHERE s.[orderAddress] LIKE @OrderName AND p.EmpName < @OrderName;
                  Note
                  The output variable must be defined when the procedure is created and also when the variable is used. The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless OrderName = variable is used.

                  Using a Table-Valued Parameter

                  The following example uses a table-valued parameter type to insert multiple rows into a table. The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. The stored procedure uses the values to insert multiple rows into a table in SQL.
                  Syntax
                  1. /* Create a table type. */
                  2. CREATE TYPE OrderType AS TABLE
                  3. ( Ordername VARCHAR(50)
                  4. , CostRate INT );
                  5. GO
                  7. /* Create a procedure to receive data for the table-valued parameter. */
                  8. CREATE PROCEDURE usp_InsertProductionLocation
                  9. @TVP OrderType READONLY
                  10. AS
                  11. SET NOCOUNT ON
                  12. INSERT INTO [sample].[Production].[Location]
                  13. ([Name]
                  14. ,[CostRate]
                  15. ,[Availability]
                  16. ,[ModifiedDate])
                  17. SELECT *, 0, GETDATE()
                  18. FROM @TVP;
                  19. GO
                  21. /* Declare a variable that references the type. */
                  22. DECLARE @LocationTVP
                  23. AS OrderType oRDER ;
                  25. /* Add data to the table variable. */
                  26. INSERT INTO @LocationTVP (LocationName, CostRate)
                  27. SELECT [Name], 0.00
                  28. FROM
                  29. [sample].[Person].[StateProvince];
                  31. /* Pass the table variable data to a stored procedure. */
                  32. EXEC usp_InsertProductionLocation @LocationTVP;
                  33. GO

                    Using an OUTPUT cursor parameter in StoredProcedure

                    The following example uses the Output cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger. First, create the procedure that declares and then opens a cursor on the OrderDetails table.
                    Syntax
                    1. CREATE PROCEDURE dbo.uspCurrencyCursor
                    2. @CurrencyCursor CURSOR VARYING OUTPUT
                    3. AS
                    4. SET NOCOUNT ON;
                    5. SET @CurrencyCursor = CURSOR
                    6. FORWARD_ONLY STATIC FOR
                    7. SELECT OrderName, orderAddress
                    8. FROM OrderDetails ;
                    9. OPEN @CurrencyCursor;
                    10. GO
                    Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

                    Modifying data by using a Stored Procedure

                    Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

                    Using Update data in a stored procedure

                    The following example uses an UPDATE statement in a stored procedure. The procedure takes one input parameter, @NewHours, and one output parameter @RowCount. The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table OrderDetails The @RowCount output parameter is used to return the number of rows affected to a local variable. The CASE expression is used in the SET clause to conditionally determine the value that is set for NewHours. When the employee is paid hourly (OrderId = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours otherwise, OrderId is set to the value specified in @NewHours.
                    Syntax
                    1. CREATE PROCEDURE Order_Details
                    2. @NewHours smallint
                    3. AS
                    4. SET NOCOUNT ON;
                    5. UPDATE OrderDetails
                    6. SET OrderId =
                    7. ( CASE
                    8. WHEN OrderId = 0 THEN OrderId + @NewHours
                    9. ELSE @NewHours
                    10. END
                    11. )
                    12. WHERE OrderId = 1;
                    13. GO
                    15. EXEC Order_Details 40;

                      Error Handling

                      Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

                      Using TRY...CATCH statement

                      The following example using the TRY...CATCH construct to return error information caught during the execution of a stored procedure.
                      Syntax
                      1. CREATE PROCEDURE OrderDetails ( @WorkOrderID int )
                      2. AS
                      3. SET NOCOUNT ON;
                      4. BEGIN TRY
                      5. BEGIN TRANSACTION
                      6. DELETE FROM OrderDetails
                      7. WHERE OrderId = @OrderID;
                      9. -- Delete the rows from the parent table, orderdetails, for the specified OrderDetails.
                      10. DELETE FROM OrderDetails
                      11. WHERE OrderId = @OrderID;
                      13. COMMIT
                      15. END TRY
                      16. BEGIN CATCH
                      17. -- Determine if an error occurred.
                      18. IF @@TRANCOUNT > 0
                      19. ROLLBACK
                      21. -- Return the error information.
                      22. DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int;
                      23. SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
                      24. RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
                      25. END CATCH;
                      27. GO
                      28. EXEC OrderDetails 13;
                      30. /* Intentionally generate an error by reversing the order in which rows
                      31. are deleted from the parent and child tables. This change does not
                      32. cause an error when the procedure definition is altered, but produces
                      33. an error when the procedure is executed.
                      34. */
                      35. ALTER PROCEDURE OrderDetails ( @OrderID int )
                      36. AS
                      38. BEGIN TRY
                      39. BEGIN TRANSACTION
                      40. -- Delete the rows from the parent table, OrderDetails, for the specified work order.
                      41. DELETE FROM OrderDetails
                      42. WHERE OrderId = @OrderID;
                      44. -- Delete rows from the child table, OrderDetails, for the specified work order.
                      45. DELETE FROM OrderDetails
                      46. WHERE OrderId = @OrderID;
                      48. COMMIT TRANSACTION
                      50. END TRY
                      51. BEGIN CATCH
                      52. -- Determine if an error occurred.
                      53. IF @@TRANCOUNT > 0
                      54. ROLLBACK TRANSACTION
                      56. -- Return the error information.
                      57. DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int;
                      58. SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
                      59. RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
                      60. END CATCH;
                      61. GO
                      62. -- Execute the altered procedure.
                      63. EXEC OrderDetails 15;
                      65. DROP PROCEDURE OrderDetails

                        Obfuscating the Procedure Definition in SQL

                        Examples in this section show how to obfuscate the definition of the stored procedure.

                        Using the WITH ENCRYPTION option

                        Syntax
                        1. CREATE PROCEDURE Sp_Employee_Name
                        2. WITH ENCRYPTION
                        3. AS
                        4. SET NOCOUNT ON;
                        5. SELECT EmpId,EmpName,EmpAddress,EmpCity
                        6. FROM EmployeeDetail ;
                        7. GO
                          The following example creates the Sp_Employee_Name procedure.

                          Forcing the Procedure to Recompile in SQL

                          Examples in this section use the Recompile clause to force the procedure to recompile every time it is executed.

                          Using the WITH RECOMPILE option in Stored_porcedure

                          The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.
                          Syntax
                          1. IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
                          2. DROP PROCEDURE dbo.uspProductByVendor;
                          3. GO
                          4. CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
                          5. WITH RECOMPILE
                          6. AS
                          7. SET NOCOUNT ON;
                          8. SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
                          9. FROM Purchasing.Vendor AS v
                          10. JOIN Purchasing.ProductVendor AS pv
                          11. ON v.BusinessEntityID = pv.BusinessEntityID
                          12. JOIN Production.Product AS p
                          13. ON pv.ProductID = p.ProductID
                          14. WHERE v.Name LIKE @Name;

                            Setting the Security Context

                            Examples in this section use the Execute as a clause to set the security context in which the stored procedure executes.

                            Using the Execute as clause statement

                            The following example shows using the execute as a clause to specify the security context in which a procedure can be executed. In the example, the option Caller specifies that the procedure can be executed in the context of the user that calls it.
                            Syntax
                            1. CREATE PROCEDURE sp_EmpoyeeDetails
                            2. WITH EXECUTE AS CALLER
                            3. AS
                            4. SET NOCOUNT ON;
                            5. SELECT v.EmpName AS Vendor, p.EmpAddress AS 'Product name',
                            6. v.EmpAddress AS 'Address',
                            7. v.EmpCity AS Availability
                            8. FROM EmployeeDetails v
                            9. INNER JOIN OrderDetails pv
                            10. ON v.OrderId = pv.EmpId
                            11. ORDER BY v.EmpName ASC;
                            12. GO

                              Creating custom permission sets in storedprocedure

                              The following example uses Execute to create custom permissions for a database operation. Some operations such as a truncate table, do not have grantable permissions. By incorporating the truncate table statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant execute permissions on the stored procedure.
                              Syntax
                              1. CREATE PROCEDURE SP_EmployeeDetails
                              2. WITH EXECUTE AS SELF
                              3. AS TRUNCATE TABLE sample..EmployeeDetails;

                              Summary

                              In this article, you learned how to use a SQL StoredProcedure statement with various options.