Do not use sp_name convention - this is for system sprocs and they exhibit slightly different behaviour- mostly in the area of visibility across dbs. usp_name or somesuch instead.
SET NOCOUNT ON; is good to have atstart of sproc body so the calling code doesnt get any rowcounts back, which may be treated as rowsets and screw everything up.