SQL Server - เก็บผลลัพธ์จาก Stored Procedure ลงในตาราง

ปกติผมก็ใช้ Stored Procedure ใน SQL Server เสมอๆครับ ซึ่งพอเรา execute stored procedure ก็จะได้ result set มาใช้งาน แต่ทีนี้ถ้าเราต้องการทำ query result set ที่ได้ละจะทำยังไงดี ถ้าเป็นเมื่อก่อนผมก็ copy stored procedure มาสร้างใหม่แล้วแก้คำสั่งข้างในให้ทำ query เพิ่มไปเลย เช่นไป join object อื่น หรือสั่ง where สั่ง group by ฯลฯ แล้วแต่ต้องการ

หรืออีกวิธีก็สั่ง CREATE TABLE ก่อน แล้วก็ INSERT ข้อมูลที่ได้จาก Stored Procedure ซึ่งวิธีนี้ถ้ามันมีหลาย field ตอนสั่ง CREATE TABLE ก็เหนื่อยไม่ใช่เล่น ซึ่งวิธีนี้ใช้บ่อยโดยเฉพาะอย่างยิ่ง stored procedure ของระบบ เช่นพวก sp_who2, sp_lock เป็นต้น ลองดูตัวอย่างครับ

CREATE TABLE #locks (spid int, dbid int, objid int,
 indid int, [type] varchar(4), resource varchar(50), mode varchar(2), status varchar(10));

INSERT INTO #locks (spid, dbid, objid, indid, [type], resource, mode, status)
EXEC dbo.sp_lock;

SELECT * FROM #locks;

DROP TABLE #locks;

อันนี้ตัวอย่างแค่ 8 fields ยังเหงื่อตก เพราะเทสกันหลายรอบครับ ทีแรกผมกำหนด datatype ไม่ถูกมันก็ error ใส่ ขนาดน้อยไปเช่น resource varchar(10) ก็ error ครับ

วันนี้ผมมีวิธีใหม่มาเสนอครับ ลองดูโค้ดละกัน

SELECT * INTO #tmpWho FROM OPENROWSET('SQLNCLI', 'Server=testSQLServer;Trusted_Connection=yes;', 'EXEC sp_who') ;

SELECT * FROM #tmpWho Where dbname='master' and loginame = 'sa';

DROP TABLE #tmpWho;

ครับ โค้ดนี้เราใช้คำสั่ง OPENROWSET ร่วมกับ SELECT INTO นั่นเอง สะดวกดีมากทีเดียว 555

คราวนี้มาดูตัวอย่างการใช้งานจริงบ้าง

SELECT * INTO tempTestOutput FROM OPENROWSET ('SQLNCLI', 'Server=testSQLServer;Trusted_Connection=yes;', 'EXEC testDB.dbo.spTestOutput ''testParameter1'''); 

SELECT t.*, e.Department, e.HiredDate
FROM tempTestOutput t INNER JOIN Employee e ON t.EmployeeId = e.EmployeeId
WHERE e.ResignedDate IS NULL
ORDER BY t.EmployeeId

คราวนี้ผมก็ได้ temp table เอาไว้ยำข้อมูลแล้วครับ เสร็จแล้วก็อย่าลืม DROP TABLE ด้วยนะ

Referrence
www.stackoverflow.com - How to SELECT * INTO [temp table] FROM [Stored Procedure]