Avatar of rocky050371
rocky050371
 asked on

Retrieve Stored Procedure Text

I have the following which lists a set of available SPs

    If Not ucProvider.SelectedRow Is Nothing Then
            Cursor = Cursors.WaitCursor
            Try

                ucProcedure.Enabled = True
                Dim cn As New OleDbConnection(_connectionMgr.Item(_callback.CallConnID).ConnConnectionString)

                Using cn
                    cn.Open()

                    Dim dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, New Object() {Nothing, Nothing, Nothing, Nothing})

                    ugParameters.DataSource = Nothing
                    ucProcedure.DataSource = Nothing
                    ucProcedure.DataSource = dt
                    ucProcedure.ValueMember = "PROCEDURE_NAME"


                End Using
                Cursor = Cursors.Default
            Catch ex As Exception
                LogException(ex)
            End Try
        Else
            MessageBox.Show("Please select a provider.", "Database Callback", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

How do I retrieve the actual stored produce code, so the user can view the contents of the SP?
.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
Ark

8/22/2022 - Mon
aplusexpert

Senthil B

command for showing sp code sp_helptext <SP name>.

or
EXAMPLE -
go to object explorer  RIGHT CLICK ON your sp click modify a new window will appear you can see the sp and edit etc...
objexplr.png
rocky050371

ASKER
I need this from the win forms app, not SQL Server
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Easwaran Paramasivam

Connect to DB that your sp resides and execute the command

sp_helptext @objectname=YourSPName.

Here sp_helptext is system stored procedure to get the text of the given SP.

Programatically connect to the database and execute the SP sp_helptext and pass the parameter @objectname value as your sp name. The @objname has datatype as nvarchar(776). You will get the result as the SP text.

Please refer http://www.codeproject.com/Articles/15403/Calling-Stored-procedures-in-ADO-NET and http://sharpertutorials.com/calling-sql-server-stored-procedures/ for more information.
ASKER CERTIFIED SOLUTION
John Claes

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ark

MsgBox (CType(ucProcedure.SelectedItem,DataRowView)("PROCEDURE_DEFINITION"))