hi,
You can do it by adding select statement at end of procedure as below
CREATE PROCEDURE MySP
(
@Type INT
)
DECLARE @strSQL AS VARCHAR(4000)
IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable
SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable';
EXECUTE(@strSQL)
select * from ##MyTmpTable
if it helps please mark it as answer