{"id":142,"date":"2019-09-24T07:51:36","date_gmt":"2019-09-24T07:51:36","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=142"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"stored-procedure-basics","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/stored-procedure-basics\/","title":{"rendered":"Stored Procedure Basics"},"content":{"rendered":"\n

Creating a simple stored procedure<\/h2>\n\n\n\n

The following SELECT<\/a><\/code> statement returns a list of products from the products<\/code> table in the BikeStores sample database<\/a>:<\/p>\n\n\n\n
1234567<\/td>SELECT product_name, list_priceFROM production.productsORDER BY product_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

To create a stored procedure that wraps this query, you use the CREATE PROCEDURE<\/code> statement as follows:<\/p>\n\n\n\n
1234567891011<\/td>CREATE PROCEDURE uspProductListASBEGIN    SELECT         product_name,         list_price    FROM         production.products    ORDER BY         product_name;END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

In this syntax:<\/p>\n\n\n\n

  • The uspProductList<\/code> is the name of the stored procedure.<\/li>
  • The AS<\/code> keyword separates the heading and the body of the stored procedure.<\/li>
  • If the stored procedure has one statement, the BEGIN<\/code> and END<\/code> keywords surrounding the statement are optional. However, it is a good practice to include them to make the code clear.<\/li><\/ul>\n\n\n\n

    Note that in addition to the CREATE PROCEDURE<\/code> keywords, you can use the CREATE PROC<\/code> keywords to make the statement shorter.<\/p>\n\n\n\n

    To compile this stored procedure, you execute it as a normal SQL statement in SQL Server Management Studio as shown in the following picture:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    If everything is correct, then you will see the following message:<\/p>\n\n\n\n
    1<\/td>Commands completed successfully.<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    It means that the stored procedure has been successfully compiled and saved into the database catalog.<\/p>\n\n\n\n

    You can find the stored procedure in the Object Explorer, under Programmability > Stored Procedures<\/strong> as shown below:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Sometimes, you need to click the Refresh<\/strong> button to manually update the database objects in the Object Explorer.<\/p>\n\n\n\n

    Executing a stored procedure<\/h2>\n\n\n\n

    To execute a stored procedure, you use the EXECUTE<\/code> or EXEC<\/code> statement followed by the name of the stored procedure:<\/p>\n\n\n\n
    1<\/td>EXECUTE sp_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    Or<\/p>\n\n\n\n
    1<\/td>EXEC sp_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    where sp_name<\/code> is the name of the stored procedure that you want to execute.<\/p>\n\n\n\n

    For example, to execute the uspProductList<\/code> stored procedure, you use the following statement:<\/p>\n\n\n\n
    1<\/td>EXEC uspProductList;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    The stored procedure returns the following output:<\/p>\n\n\n\n

    \"SQL<\/figure>\n\n\n\n

    Modifying a stored procedure<\/h2>\n\n\n\n

    To modify an existing stored procedure, you use the ALTER PROCEDURE<\/code> statement.<\/p>\n\n\n\n

    First, open the stored procedure to view its contents by right-clicking the stored procedure name and select Modify<\/strong> menu item:<\/p>\n\n\n\n

    \"SQL<\/figure>\n\n\n\n

    Second, change the body of the stored procedure by sorting the products by list prices instead of product names:<\/p>\n\n\n\n
    1234567891011<\/td> ALTER PROCEDURE uspProductList    AS    BEGIN        SELECT             product_name,             list_price        FROM             production.products        ORDER BY             list_price     END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    Third, click the Execute<\/strong> button, SQL Server modifies the stored procedure and returns the following output:<\/p>\n\n\n\n
    1<\/td>Commands completed successfully.<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    Now, if you execute the stored procedure again, you will see the changes taking effect:<\/p>\n\n\n\n
    1<\/td>EXEC uspProductList;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    The following shows the partial output:<\/p>\n\n\n\n

    \"SQL<\/figure>\n\n\n\n

    Deleting a stored procedure<\/h2>\n\n\n\n

    To delete a stored procedure, you use the DROP PROCEDURE<\/code> or DROP PROC<\/code> statement:<\/p>\n\n\n\n
    1<\/td>DROP PROCEDURE sp_name;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    or<\/p>\n\n\n\n
    1<\/td>DROP PROC sp_name;    <\/td><\/tr><\/tbody><\/table>\n\n\n\n

    where sp_name<\/code> is the name of the stored procedure that you want to delete.<\/p>\n\n\n\n

    For example, to remove the uspProductList<\/code> stored procedure, you execute the following statement:<\/p>\n\n\n\n
    1<\/td>DROP PROCEDURE uspProductList;<\/td><\/tr><\/tbody><\/table>\n","protected":false},"excerpt":{"rendered":"

    Creating a simple stored procedure The following SELECT statement returns a list of products from the products table in the BikeStores sample database: 1234567 SELECT product_name, list_priceFROM production.productsORDER BY product_name; To create a stored procedure that wraps this query, you… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,18,19],"tags":[],"_links":{"self":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/142"}],"collection":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/comments?post=142"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/142\/revisions"}],"predecessor-version":[{"id":143,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/142\/revisions\/143"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=142"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}