ALTER PROCEDURE sp @foo VARCHAR(250), @bar VARCHAR(250) AS 

BEGIN TRAN

DECLARE @baz NUMERIC, @quux NUMERIC

SELECT @baz = col FROM table1 WHERE condition
SELECT @quux = col FROM table2 WHERE condition = @baz

INSERT INTO table3 (col1, col2, col3) 
VALUES (@foo, @bar, @quux)

UPDATE someother nonsense

COMMIT TRAN