This blog has moved to http://ikhwanhayat.net

Tuesday, August 02, 2005

Accessing Stored Procedure Output In A SELECT Statement

I've been wanting to do this for a long time! And finally found it. The problem is how to pass the output from a stored proc as the FROM part in a SELECT statement. I want to do something like

SELECT loginame, status
FROM (EXEC master.dbo.sp_who)
But this line just would never run.. Finally found this today,
SELECT  loginame, status
FROM OPENROWSET ('SQLOLEDB', 
                 'Server=(local);TRUSTED_CONNECTION=YES;', 
                 'set fmtonly off exec master.dbo.sp_who')
From here: Ward Pond's SQL Server blog : "The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement". It does have it's penalty and limitation, but nonetheless it's amazing to know that it can be done.

11 Comments:

At 8/02/2005 12:02:00 pm, Blogger kitchenkidscoffee said...

menarik jugak ni, nnti blehlah aku ckp kat supervisor aku.." abg mat, ni ada satu mamat dia buat gini2..saya pun xbrp paham..nnti rujuklah kat blog dia..". hehe.

 
At 8/02/2005 12:25:00 pm, Blogger Ikhwan Hayat said...

ooooh abg mat ke nama dia..

 
At 8/02/2005 03:16:00 pm, Blogger kitchenkidscoffee said...

kalo ko dh ada stored proc, buat apa nk pggil dengan select lg?

 
At 8/02/2005 04:18:00 pm, Blogger Ikhwan Hayat said...

buat apa? buat bodoh...
muahahah..

ok, serius.. serius.. hmmm..

katakan kau ada satu SP bernama spGetCustomer yg tugasnya mengorek dari table user tables dan listkan customer je.

Lepas tu tiba2 dlm SP lain kau mahu listkan customer's name (nama sahaja) yg tinggal di KL sahaja, so boleh la buat mcm ni:

SELECT Tbl.[Name]
FROM OPENROWSET ('SQLOLEDB',
'Server=(local);TRUSTED_CONNECTION=YES;',
'set fmtonly off exec mydata.dbo.spGetCustomer') AS Tbl
WHERE Tbl.State = 'KL'

lebih kurang laa.. tapi kena ambil kira penalty yg ada jugak, sama ada sesuai guna atau tak..

 
At 8/02/2005 05:29:00 pm, Blogger kitchenkidscoffee said...

kenapa xbuat je:

select name from table where add=KL

mcm lebih kurang je aku nmpak. ke sbb mmg nk guna jgk sp tu? stored proc ni lebih mengurangkan proses ke drpd create on the fly je?

 
At 8/02/2005 05:46:00 pm, Blogger kitchenkidscoffee said...

-stored proc ni lebih mengurangkan
-proses ke drpd create on the fly
-je?

op..op. aku tlh menjawab soalan sendiri. ye..lebih laju.

so, aku agak ko nk guna sp jugak pasal lebih laju

 
At 8/02/2005 05:52:00 pm, Blogger fuza said...

mencelah jap!! En 1khz, dah jumpa x sesiapa yg eligible utk request saya tu?

 
At 8/02/2005 11:34:00 pm, Blogger Ikhwan Hayat said...

kalaulah ia semudah itu kak long..

masalahnya dalam spGetCustomer tu, bukan setakat

SELECT * FROM Customer

je.. tapi lebih dahsyat dari itu..

ye la, bila kita dah buat normalization dlm db schema kita, kemungkinan besar (mmg pasti kot) kita mesti kena buat bermacam2 JOIN antara table..

dlm sistem yg aku tgh buat skarang ni, kalau takat join 3-4 table tu biasa dah la.. join nak dekat 10 table pun ada..

jadi, mungkin bila nak create customer, kita kena join table User dgn UserDetails, pastu filter pulak dgn UserType = 'C', pastu dan sebagainya lah.. (nama2 table hanya rekaan semata2, tiada kaitan dgn yg hidup mewah atau yg sudah meninggalkan dunia fana ini)..

jadi panjang la.. boleh juga copy statements yg pjg2 tu dlm SP yg baru tu.. tapi biasa la, dasar orang pemalas (baca: penyokong kuat konsep OnceAndOnlyOnce)

Dan selain itu dalam certain case mcm contoh dlm entry tu, kita gunakan hasil dari SP yg sedia ada dlm MSSQL tu (SP mcm ni ada prefix "sp_" mcm "sp_who")..

PERHATIAN: solution dlm entry tu bukan oleh ikut bulat2, kena kaji pros and cons nya.. Cons paling besar kat situ ialah OPENROWSET akan buka satu lagi connection ke server.. bukak byk tak bagus kak..

 
At 8/02/2005 11:37:00 pm, Blogger Ikhwan Hayat said...

oh tambah sikit, SP mmg sepatutnya lebih laju dari on-the-fly sql stmts..
sebab dia dah compile dulu kat server..
tapi beza dia tak significant rasanya..

fuza: beluumm... kan dah cakap jgn panggil "Encik".. panggil "Abang" 1kHz je dah la.. muahahaha (gelak lagi..)

 
At 8/03/2005 09:53:00 am, Blogger kitchenkidscoffee said...

-kalaulah ia semudah itu kak long..

ops..aku bkn kak long. fuza kak long. aku mmg xnmpak lg kalo kompleks jadi mcm mana.

-(SP mcm ni ada prefix "sp_"
-mcm "sp_who")..

yep, aku tau pasal prefix ni

-Cons paling besar kat situ ialah
-OPENROWSET akan buka satu lagi
-connection ke server

i'll try 2 rmmber this.

thnx!

 
At 10/30/2005 03:04:00 pm, Anonymous Anonymous said...

Thanks for the link! -Ward Pond

 

Post a Comment

<< Home