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
Printable View
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
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:Quote:
Originally Posted by eranfox
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'
Hello umilimi81,Quote:
Originally Posted by umilmi81
I think you did not notice the brackets on the word good regarding the search on the MSDN. :wave:
ERAN