Another "funny" thing is that BEGIN...END does not define the body of the procedure. You can have multiple BEGIN...END in the body of a procedure, like this:
create proc testas
begin
select 1
end
begin
select 2
endExecuting "test" will return two recordsets.
A procedure can have an empty body, but BEGIN...END cannot be empty.
This one is pretty interesting. I really didn't know that by adding Begin, End (twice or more), we can return 2 recordsets. But I dont think this type of proc can ever be used.
Has anybody tried using sp_spaceused (returns 2 recordsets) in a select query ? Its just not possible.