Great point. Amazing, the compiler see two creations of the #tmpID table and stops the procedure from compiling.

Do you have at hand some best practices for using DML statements? I am thinking on something like:

CREATE PROCEDURE QOTD (@source INT)

AS

BEGIN

DECLARE @strQueryDML AS VARCHAR(100)

IF @source = 1

SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl1'

ELSE

SET @strQueryDML = 'SELECT ID INTO #tmpID2 FROM tbl2'

EXECUTE (@strQueryDML)

SELECT ID FROM #tmpID2

END