| Author |
Topic |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
 Posted - 2004-11-08 : 20:29:38
|
What is the syntax for sending an sp's result set to a table? |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-11-08 : 20:56:53
|
| my sp's have their Select clause in TSQL code and I need to list all of their field names Like the following procedureInsert INTO #MyFieldNames exec usp_crcreports_advisor_notes_monthly_tally @Program, @Acad_Year, @Meeting_Type, @Advisor_Meeting_Date, @Advisor_Marshall_IdSelect c.name from Tempdb.dbo.sysobjects o Inner Join Tempdb.dbo.syscolumns c on o.id = c.id Where o.name = '#MyFieldNames'order by c.colid |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-08 : 22:40:48
|
use function, and return a table  --------------------keeping it simple... |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-11-09 : 12:22:01
|
| Could you expand on that concept...I am looking for the field names... If I query the sysobjects and cols the table is not in the catalog. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-09 : 14:54:00
|
| Well you can look at the code to get the field names.Otherwise the resultset format isn't held anywhere so you can't get them without running the sp.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-11-09 : 16:26:50
|
| Try:SELECT * into #tmp FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=myserver;', 'EXEC master.dbo.myproc') if it will run without paramaters or if you can hard-code them. Or maybe:DECLARE @cursor integer--Create server side cursor to open procedureEXEC sp_cursoropen @cursor output, N'exec usp_crcreports_advisor_notes_monthly_tally ', @Program, @Acad_Year, @Meeting_Type, @Advisor_Meeting_Date, @Advisor_Marshall_Id--Name cursorEXEC sp_cursoroption @cursor, 2, 'myCursor'--Cursor columns are stored in master..syscursorcolumns for server cursors. type_name() returns UDT nameSELECT column_name, type_name(data_type_sql) TypeNameFROM master.dbo.syscursorcolumns WHERE cursor_handle = @cursor--Close cursor. Retrieve no rows.exec sp_cursorclose @cursor --KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-11-09 : 16:28:42
|
| Ouch... Thank you for helping any way... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-10 : 00:43:56
|
quote:
Originally posted by clarkbaker1964
Could you expand on that concept...I am looking for the field names... If I query the sysobjects and cols the table is not in the catalog.
i expect this is the sp or fxn in question?Insert INTO #MyFieldNames exec usp_crcreports_advisor_notes_monthly_tally @Program, @Acad_Year, @Meeting_Type, @Advisor_Meeting_Date, @Advisor_Marshall_Idand it's returning a table?--------------------keeping it simple... |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-11-10 : 17:23:53
|
| Hi Jen...my sp - usp_crcreports_advisor_notes_monthly_tally is returning a recordsetwe have many sp's that are returning aliases that are not standardized and this is leading to confusionSo I'm trying to find an easy way to look at the returning field names accross all objects (views/sp) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-10 : 20:07:57
|
| if i read you right, you're looking for the parameters or the returning table fields?if parameters:select c.[name] from syscolumns cjoin sysobjects oon c.id=o.idwhere o.name='spname' and patindex(@%',c.[name])>0if for returned fields:select c.[name] from syscolumns cjoin sysobjects oon c.id=o.idwhere o.name='fnsplitname' and patindex('@%',c.[name])=0--------------------keeping it simple... |
|
|
|
|
sp results to temp