How to read SQL Stored Proc Data?

[jesus4u]

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

[eranfox]

Hello jesus4u,
Look Here
Is that what you need?

Best Regards,
ERAN

[jesus4u]

it might be thanks

[eranfox]

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.


ERAN

[brucevde]

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.

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

[umilmi81]

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

[szlamany]

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

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'

[eranfox]

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

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