How to read SQL Stored Proc Data?

I want to create a function that can read my stored procs parameters and return their names to me. I am using ADO.

Is this possible?

Thanks

Hello jesus4u,
Look Here
Is that what you need?

Best Regards,
ERAN

it might be thanks

Hello again,
Dont thank me its from the MSDN...

Basically, when i search for (good) answers i start with MSDN
Then i search through Google and when i know i'm stuck i go directly to VBForums.com to look for the answer.

In VBForums i know i can find the answers to my questions and even more i know i will get few different answers onthe same question.

:wave:
ERAN

The link posted by eranfox shows you how to create and execute a command object.

Here are two ways to retrieve information about the parameters of a stored procedure.

VB Code:

Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim strConn As String
 
    strConn = "provider=sqloledb;data source=handel;initial catalog=northwind;integrated security=sspi"
 
   'get stored procedure parameter information using sp_sproc_columns system stored procedure
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "sp_sproc_columns 'CustOrderHist'", strConn, adOpenStatic, adLockReadOnly
    Set rs.ActiveConnection = Nothing
    
    Do Until rs.EOF
        Debug.Print rs.Fields("COLUMN_NAME").Value, rs.Fields("TYPE_NAME").Value, rs.Fields("LENGTH").Value
        rs.MoveNext
    Loop
    
    rs.Close
 
    'auto populate the command.parameters collection using the Refresh method.
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = strConn
        .CommandText = "CustOrderHist"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        
        For Each prm In .Parameters
            Debug.Print prm.Name, prm.Type, prm.Size
        Next
    End With

Quote:

---

Originally Posted by eranfox

Basically, when i search for (good) answers i start with MSDN
Then i search through Google and when i know i'm stuck i go directly to VBForums.com to look for the answer.

---

I do the opposite. I find MSDN lacking on good information. Or even if they do have it, it's scattered across 10 pages :mad:

Do you mean that you want a SPROC that will tell you the parameters of a given SPROC in the database?

Code:

---

USE Stufiles
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSqlSP]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSqlSP]
GO
CREATE PROCEDURE GetSqlSP
        @StoredProc varchar(50)
AS
Select Parameter_Name,Data_Type,Parameter_Mode,Character_Maximum_Length
From INFORMATION_SCHEMA.Parameters
Where SPECIFIC_NAME = @StoredProc
Go
GRANT EXECUTE ON GetSqlSP TO StufilesUser
Go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--execute GetSqlSP 'frmContribution_View'

---

Quote:

---

Originally Posted by umilmi81

I do the opposite. I find MSDN lacking on good information. Or even if they do have it, it's scattered across 10 pages :mad:

---

Hello umilimi81,
I think you did not notice the brackets on the word good regarding the search on the MSDN. :wave:
ERAN