I tried another simple stored procedure above, which I executed. It didn't display the results window for the SELECT statement result at this time.
Not sure which proc with what parameters.
Then I right clicked the procedure and did execute procedure and got this code (with the results window this time):
The procedure is returning an int value to @return_value. But I don't understand the purpose of this SELECT statement and what it is doing. Why is SQL taking a text value ('Return Value') and assigning the value of @return_value to the text string? I guess I could understand if it were stated with the variable first, i.e. @return_value = 'Return Value' but then that wouldn't make sense since @return_value is supposed to be of type int instead of text.
*sigh* this is another unfortunate piece of Microsoft boilerplate code.
First, regarding procedure return values in general: all stored procedures have an integer return value, if nothing went wrong (or it was not overridden with an explicit return value) it will be 0. If it's not 0, then there was an issue. There are some really excellent stairways and articles on SSC which explain stored procedure execution. Definitely worth a look.
The select statement does looks strange to me too. Here's some code:
declare @return_value int=123;
/* these two queries return the same result set */
select 'Return Value' = @return_value;
select @return_value [Return Value];
Due to the useless capitalization, the space in the name assigned to the variable (please don't ever), the goofy syntax, and the lack of semicolons this is just a sad unfortunate piece of code. My general advice to people learning sql is not to learn it from Microsoft. Sites like SSC are far better promoters of best practices. The Sql Server sample applications are good but the documents that explain them... not so much.