Search results matching tags 'Best Practices', 'habits', and 'output'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Best+Practices,habits,output&orTags=0Search results matching tags 'Best Practices', 'habits', and 'output'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Bad habits to kick : using SELECT or RETURN instead of OUTPUThttp://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output.aspxFri, 09 Oct 2009 20:01:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:17475AaronBertrand<p><i>In my <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-using-dashes-and-spaces-in-entity-names.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-using-dashes-and-spaces-in-entity-names.aspx" target="_blank">last post in this series</a>,
I
covered the use of "bad" characters in entity names, such as spaces
or dashes.&nbsp; In this post I will talk about using RETURN and OUTPUT inappropriately.<br></i></p>
<p><i></i>Jamie Thomson touched on part of this pet peeve in response to one of the other posts in this series.&nbsp; So let me ask, do you see anything wrong with this procedure?<br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE&nbsp;PROCEDURE&nbsp;</font><font color="black">dbo.foo<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">DECLARE&nbsp;</font><font color="#434343"><br> @hr&nbsp;</font><font color="blue">INT</font><font color="gray">,<br> </font><font color="#434343">@rc&nbsp;</font><font color="blue">INT</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">EXEC&nbsp;</font><font color="#434343">@hr = </font><font color="blue"></font><font color="black">dbo.</font><font color="darkred">sp_columns&nbsp;</font><font color="red">'dbo.bar'</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SELECT&nbsp;</font><font color="#434343">@rc = </font><font color="blue"></font><font color="magenta">@@ROWCOUNT</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SELECT </font><font color="black">hr = </font><font color="#434343">@hr</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">RETURN&nbsp;</font><font color="gray">(</font><font color="#434343">@rc</font><font color="gray">);<br></font><font color="blue">END<br>GO</font><font color="black"></font></pre>
</td>
</tr>
</table>
</blockquote>
<p>The title of this post kind of gives it away, but I thought it would fun to ask anyway.&nbsp; Here is what I see wrong: <br></p>
<ol>
<li><a href="http://msdn.microsoft.com/en-us/library/ms189499.aspx" title="http://msdn.microsoft.com/en-us/library/ms189499.aspx" target="_blank">SELECT</a> should be used for returning resultsets, not scalars.&nbsp; This procedure uses a SELECT statement to return a single value to the client.&nbsp; This is inefficient because most applications will have to prepare additional objects (typically referred to as "recordsets") and other support in order to consume the result.&nbsp; While it is certainly valid syntax to use SELECT to return scalar values, this does not need to be common in production code.&nbsp; This is the kind of thing that can make it slightly harder for high-end applications to scale.<br><br>While not definitive proof that this is bad, and while I use SELECT for multiple variable assignment, IIRC the standard does not allow SELECT without FROM.&nbsp; And in the <a href="http://msdn.microsoft.com/en-us/library/ms187731.aspx" title="http://msdn.microsoft.com/en-us/library/ms187731.aspx" target="_blank">26 SELECT examples in Books Online</a>, not one of them references scalars, at least at the time of writing.<br><br></li>
<li><a href="http://msdn.microsoft.com/en-us/library/ms174998.aspx" title="http://msdn.microsoft.com/en-us/library/ms174998.aspx" target="_blank">RETURN</a> is for exiting the procedure, and is intended for returning error / status codes, not data.&nbsp; Do not use this to send back to the caller the latest IDENTITY value generated or the row count from an operation.&nbsp; Note that you are restricted to using integer-based data anyway, since you cannot change the data type of the value passed back by RETURN().&nbsp; For more information, see the topic "<a href="http://msdn.microsoft.com/en-us/library/ms190778.aspx" title="http://msdn.microsoft.com/en-us/library/ms190778.aspx" target="_blank">Returning Data by Using a Return Code</a>" in Books Online.<br><br></li>
<li><a href="http://msdn.microsoft.com/en-us/library/ms191422.aspx" title="http://msdn.microsoft.com/en-us/library/ms191422.aspx" target="_blank">OUTPUT</a> is designed for returning scalar values (oh, and <a href="http://msdn.microsoft.com/en-us/library/ms175498.aspx" title="http://msdn.microsoft.com/en-us/library/ms175498.aspx" target="_blank">cursors</a>, but who does that?).&nbsp; In the example above, we are sending two scalar values back to the caller (though, in fact, one should be a RETURN value), yet we are not using an OUTPUT parameter at all!&nbsp; For more information, see the topic "<a href="http://msdn.microsoft.com/en-us/library/ms187004.aspx" title="http://msdn.microsoft.com/en-us/library/ms187004.aspx" target="_blank">Returning Data by Using OUTPUT Parameters</a>" in Books Online.&nbsp; As an aside, I suggest always using the full word OUTPUT; you may be tempted to just type OUT but personally I find this lazy shorthand that could prove troublesome later - for example, if you have to search your codebase for all uses of OUTPUT parameters.<br></li>
</ol>
<p>Yes, using OUTPUT makes it a little more complex to develop and debug stored procedures, since you have to declare your output parameters up front.&nbsp; I am not against using SELECT for scalars while developing.&nbsp; But there is no reason to deploy them that way.&nbsp; You can even use a @debug parameter to switch the methodology depending on the scenario, e.g.: <br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.foo<br>&nbsp;&nbsp;</font><font color="#434343">@debug </font><font><font color="blue"> BIT</font></font><font color="black"> = </font><font color="black">0</font><font color="gray">,<br>&nbsp;&nbsp;</font><font color="#434343">@rc&nbsp;</font><font color="blue"> INT </font><font color="black">= </font><font color="gray">NULL</font><font color="blue"> OUTPUT</font><font color="gray"><br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;SET NOCOUNT</font><font color="black"> </font><font color="blue">ON</font><font color="gray">;<br><br>&nbsp;&nbsp;</font><font color="blue">DECLARE&nbsp;</font><font color="#434343">@hr&nbsp;</font><font color="blue">INT</font><font color="gray">;<br><br>&nbsp;&nbsp;</font><font color="blue">EXEC&nbsp;</font><font color="#434343">@hr = </font><font color="black">dbo.</font><font color="darkred">sp_columns&nbsp;</font><font color="red">'dbo.bar'</font><font color="gray">;<br><br>&nbsp;&nbsp;</font><font color="blue">SET&nbsp;</font><font color="#434343">@rc = </font><font color="blue"></font><font color="magenta">@@ROWCOUNT</font><font color="gray">;<br><br>&nbsp;&nbsp;</font><font color="blue">IF&nbsp;</font><font color="#434343">@debug = </font><font color="blue"></font><font color="black">1<br></font><font color="blue"> BEGIN<br> SELECT&nbsp;</font><font color="black">hr =</font><font color="blue"> </font><font color="#434343">@hr</font><font color="gray">,&nbsp;</font><font color="black">rc = </font><font color="blue"></font><font color="#434343">@rc</font><font color="gray">;<br>&nbsp; </font><font color="blue">END<br><br> RETURN&nbsp;</font><font color="gray">(</font><font color="#434343">@hr</font><font color="gray">);<br></font><font color="blue">END<br>GO</font><font color="black"></font></pre>
</td>
</tr>
</table>
</blockquote>
<p>Now you can call this stored procedure with or without specifying the OUTPUT parameter up front.&nbsp; (I use this @debug technique for a lot of debugging elements, including cases where using the flag can change the plan - in the normal case this doesn't really hurt anything because the production plan is the one that is in the cache 99.99% of the time.) <br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="green">--&nbsp;debug&nbsp;mode:<br></font><font color="blue"><br>EXEC&nbsp;</font><font color="black">dbo.foo&nbsp;</font><font color="#434343">@debug = </font><font color="black">1</font><font color="gray">;<br><br><br></font><font color="green">--&nbsp;normal&nbsp;operation:<br><br></font><font color="blue">DECLARE&nbsp;</font><font color="#434343"><br> @rc&nbsp;</font><font color="blue">INT</font><font color="gray">,<br> </font><font color="#434343">@hr&nbsp;</font><font color="blue">INT</font><font color="gray">;<br><br></font><font color="blue">EXEC&nbsp;</font><font color="#434343">@hr = </font><font color="blue"></font><font color="black">dbo.foo&nbsp;</font><font color="#434343">@rc = </font><font color="blue"></font><font color="#434343">@rc&nbsp;</font><font color="blue">OUTPUT</font><font color="gray">;<br></font><font color="blue"><br>PRINT&nbsp;</font><font color="#434343">@hr</font><font color="gray">;<br></font><font color="blue">PRINT&nbsp;</font><font color="#434343">@rc</font><font color="gray">;</font></pre>
</td>
</tr>
</table>
</blockquote>
You'll notice that I laced the above commentary with several links to Books Online topics.&nbsp; I highly recommend becoming very familiar with when and where you should use RETURN, OUTPUT and SELECT to return data from a stored procedure.
<p><i>I am working on a series of "Bad habits to kick" articles, in an
effort to motivate people to drop some of the things that I hate to see
when I inherit code.&nbsp; Up next: <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list.aspx" target="_blank">using SELECT * / omitting the column list</a>.</i>&nbsp; <br></p>