{"id":144,"date":"2019-09-24T07:52:16","date_gmt":"2019-09-24T07:52:16","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=144"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"output-parameters","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/output-parameters\/","title":{"rendered":"Output Parameters"},"content":{"rendered":"\n<h2>Creating output parameters<\/h2>\n\n\n\n<p>To create an output parameter for a&nbsp;<a href=\"http:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/\">stored procedure<\/a>, you use the following syntax:<\/p>\n\n\n\n<table class=\"wp-block-table\"><tbody><tr><td>1<\/td><td>parameter_name data_type OUTPUT<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p>A stored procedure can have many output parameters. In addition, the output parameters can be in any valid&nbsp;<a href=\"http:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-data-types\/\">data type<\/a>&nbsp;e.g.,&nbsp;<a href=\"http:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-int\/\">integer<\/a>,&nbsp;<a href=\"http:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-date\/\">date<\/a>, and&nbsp;<a href=\"http:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-varchar\/\">varying character<\/a>.<\/p>\n\n\n\n<p>For example, the following stored procedure finds products by model year and returns the number of products via the&nbsp;<code>@product_count<\/code>&nbsp;output parameter:<\/p>\n\n\n\n<table class=\"wp-block-table\"><tbody><tr><td>123456789101112131415<\/td><td>CREATE PROCEDURE uspFindProductByModel (&nbsp;&nbsp;&nbsp;&nbsp;@model_year SMALLINT,&nbsp;&nbsp;&nbsp;&nbsp;@product_count INT OUTPUT) ASBEGIN&nbsp;&nbsp;&nbsp;&nbsp;SELECT &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;product_name,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;list_price&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;production.products&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;model_year = @model_year;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @product_count = @@ROWCOUNT;END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p>In this stored procedure:<\/p>\n\n\n\n<p>First, we created an output parameter named&nbsp;<code>@product_count<\/code>&nbsp;to store the number of products found:<\/p>\n\n\n\n<table class=\"wp-block-table\"><tbody><tr><td>1<\/td><td>@product_count INT OUTPUT<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p>Second, after the&nbsp;<code>SELECT<\/code>&nbsp;statement, we assigned the number of rows returned by the query(<code>@@ROWCOUNT<\/code>) to the&nbsp;<code>@product_count<\/code>&nbsp;parameter.<\/p>\n\n\n\n<p>Once you execute the&nbsp;<code>CREATE PROCEDURE<\/code>&nbsp;statement&nbsp;above, the&nbsp;<code>uspFindProductByModel<\/code>&nbsp;stored procedure is compiled and saved in the database catalog.<\/p>\n\n\n\n<p>If everything is fine, SQL Server issues the following output:<\/p>\n\n\n\n<table class=\"wp-block-table\"><tbody><tr><td>1<\/td><td>Commands completed successfully.<\/td><\/tr><\/tbody><\/table>\n\n\n\n<h2>Calling stored procedures with output parameters<\/h2>\n\n\n\n<p>To call a stored procedure with output parameters, you follow these steps:<\/p>\n\n\n\n<ul><li>First, declare&nbsp;<a href=\"http:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/variables\/\">variables<\/a>&nbsp;to hold the value returned by the output parameters<\/li><li>Second, use these variables in the stored procedure call.<\/li><\/ul>\n\n\n\n<p>For example, the following statement executes the&nbsp;<code>uspFindProductByModel<\/code>&nbsp;stored procedure:<\/p>\n\n\n\n<table class=\"wp-block-table\"><tbody><tr><td>1234567<\/td><td>DECLARE @count INT;&nbsp;EXEC uspFindProductByModel&nbsp;&nbsp;&nbsp;&nbsp;@model_year = 2018,&nbsp;&nbsp;&nbsp;&nbsp;@product_count = @count OUTPUT;&nbsp;SELECT @count AS &#8216;Number of products found&#8217;;<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p>The following picture shows the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Output-Parameter-Example.png\" alt=\"SQL Server Stored Procedure Output Parameter Example\" class=\"wp-image-813\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Creating output parameters To create an output parameter for a&nbsp;stored procedure, you use the following syntax: 1 parameter_name data_type OUTPUT A stored procedure can have many output parameters. In addition, the output parameters can be&hellip; <\/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\/144"}],"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=144"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/144\/revisions"}],"predecessor-version":[{"id":145,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/144\/revisions\/145"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=144"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}