Professional Web Applications Themes

Retrieving SQL Server SPs Return Recordset Column Names - Microsoft SQL / MS SQL Server

Dim RS as ADODB.Recordset .... .... .... RS(0).name should give you "emp_id" and RS(1).name will give you "emp_name". Default collection of ADODB.Recordset is Fields from which you can get the number of fields in the recordset: RS.fields.count -- Dean Savovic www.teched.hr "Maher K. Al-Jendasi" <fm> wrote in message news:069501c35021$b82410a0$gbl... ...

  1. #1

    Default Re: Retrieving SQL Server SPs Return Recordset Column Names

    Dim RS as ADODB.Recordset
    ....
    ....
    ....
    RS(0).name should give you "emp_id"
    and RS(1).name will give you "emp_name".

    Default collection of ADODB.Recordset is Fields from which you can get the number of fields in the recordset:
    RS.fields.count

    --
    Dean Savovic
    www.teched.hr


    "Maher K. Al-Jendasi" <fm> wrote in message news:069501c35021$b82410a0$gbl... 


    Dean Guest

  2. #2

    Default Re: Retrieving SQL Server SPs Return Recordset Column Names

    If your procedure is as simple as this one the you can use:



    create table Employees
    (emp_id int identity(1,1)
    , emp_name char(30))

    go

    CREATE PROC GetEmployees

    AS

    SELECT emp_id, emp_name
    FROM Employees

    GO

    insert into Employees (emp_name)
    select 'John'
    union
    select 'Henry'

    select
    ltrim(rtrim(substring(text, charindex(' ', text, charindex('select' , text)), charindex('from', text) - charindex(' ', text,
    charindex('select' , text)))))
    from syscomments
    where object_name(id) = 'GetEmployees'

    drop proc GetEmployees

    drop table Employees

    Otherwise you should keep in mind that system table sysomments for stored procedures with more than 4000 characters has more than
    one row (one row
    for each 4000 chars).

    You could try the firs method but if you don't want to get all of the records first execute SQL statement "set rowcount 1"
    with your command object (ADODB.Command) and then execute the stored procedure which shoul return only one record.

    Then use
    RS(0).name and RS(1).name



    --
    Dean Savovic
    www.teched.hr


    "Maher K. Al-Jendasi" <fm> wrote in message news:phx.gbl... 
    > number of fields in the recordset: 
    > news:069501c35021$b82410a0$gbl... 
    > >
    > >[/ref]
    >
    >[/ref]


    Dean Guest

  3. #3

    Default Re: Retrieving SQL Server SPs Return Recordset Column Names

    Hello Dean,

    This mean that I have to execute the procedure and know the names of
    returned fields through Fields collection. This method have some performance
    issues, such as it must be execute first, and what if this procedure return
    say 100000 records???

    What I need is knowing the names of returned columns directly through ADO,
    or ADOX, or if there a SQL Server procedure that can do this for me.

    Regards,


    Maher K. Al-Jendasi
    fm

    "Dean Savovic" <hr> wrote in message
    news:bfiqpk$e4f$net4u.hr... 
    number of fields in the recordset: 
    news:069501c35021$b82410a0$gbl... 
    >
    >[/ref]


    Maher Guest

  4. #4

    Default Re: Retrieving SQL Server SPs Return Recordset Column Names

    Check out the SET FMTONLY ON option in SQL Server. This lets you get only
    the metadata and not any rows. Remember to make it OFF immediately,
    otherwise none of your queries will return any data :-)
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Maher K. Al-Jendasi" <fm> wrote in message
    news:phx.gbl... 
    performance 
    return [/ref]
    the 
    > news:069501c35021$b82410a0$gbl... 
    > >
    > >[/ref]
    >
    >[/ref]


    SriSamp Guest

  5. #5

    Default Re: Retrieving SQL Server SPs Return Recordset Column Names

    Hello Dean,

    What if my SP is an action query (for example DELETE ), if I execute it then
    it will perform the action; causing data loss. In this case how I could know
    whether it a action query or not???

    Regards,


    Maher K. Al-Jendasi
    fm
    "Dean Savovic" <hr> wrote in message
    news:bfjfl5$r08$net4u.hr... 
    news:phx.gbl... [/ref][/ref]
    only [/ref][/ref]
    through [/ref][/ref]
    get 
    > >
    > >[/ref]
    >
    >[/ref]


    Maher Guest

  6. #6

    Default Re: Retrieving SQL Server SPs Return Recordset Column Names

    Why would you want to execute DELETE store procedure in this manner. DELETE statement will not return a recordset, and
    if I remember correctly, you wan't to find out recordset field names.

    If you have DELETE and SELECT (that returns recodrset) statements in your stored procedures then you might consider changing
    your sp implementation.

    --
    Dean Savovic
    www.teched.hr


    "Maher K. Al-Jendasi" <fm> wrote in message news:phx.gbl... 
    > news:phx.gbl... [/ref]
    > only [/ref]
    > through [/ref]
    > get 
    > >
    > >[/ref]
    >
    >[/ref]


    Dean Guest

Similar Threads

  1. Getting column data without column names
    By Jared@Itron in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 18th, 09:26 PM
  2. recordset table names problem
    By bcremeans in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 17th, 01:51 PM
  3. counting column and getting column names
    By dduck1934 in forum Coldfusion Database Access
    Replies: 0
    Last Post: May 24th, 02:05 PM
  4. Retrieving Images from Recordset
    By Barney... in forum Dreamweaver AppDev
    Replies: 2
    Last Post: May 24th, 08:22 AM
  5. Replies: 2
    Last Post: December 4th, 04:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139