There are basically three ways to return data from a stored procedure.
1. (The version you are using in the stored procedure itself): just run a SELECT statement. The result will be returned in the form of a result set to the client. The client is not the same as the procedure calling the stored procedure - the client is SSMS when testing code in SSMS, and often is some .Net application in the final version of the code. That's where the result set will go. If a stored procedure calls a second stored procedure, it will not see any data returned from that second stored procedure in this way.
(** exception - if you call a stored procedure using INSERT ... EXECUTE, you will be able to capture these results)
2. (Recommended for scalar values): as an output parameter. Declare the parameter like you do a normal parameter, but add the OUTPUT property. Add the same OUTPUT property to the variable you pass in when calling the stored procedure. The procedure has to assign a value to that parameter, and that value will then also be assigned to the variable used when the stored procedure is called. Examples of this can be found in Books Online.
3. (The version you are using in the call of the stored procedure, used for a single numeric value and recommended to be used only for success or failure): as the return code of the stored procedure. This is set in the stored procedure by using the RETURN [Expression]; statement. (Leave out expression or let flow of controle reach the end of the procedure and 0 will be returned). The caller can grab the value by using Exec @variable = dbo.ProcName;
I hope this helps. Books Online has lots of examples on all of these.