Reading the contents of Stored Procedures from MS SQL Server 2000 through VB.net

[Aarvee]

I need help in reading the contents of the stored procedures in MS SQL Server through VB.Net. If the contents can be read as string or text file it is OK.

Any guidance would be highly useful.

Thanks and Best Regards


Varadarajan R

 

[jmcilhinney]

Sounds like you need to do some reading on ADO.NET. You can create an SqlCommand object and set its CommandText property to the name of the SP and its CommandType property to StoredProcedure. You can then call ExecuteReader or ExecuteScalar on the command directly or assign it to the SelectCommand property of an SqlDataAdapter and call Fill.

 

[TechGnome]

Hmmm.... just goest oshow how two people can read the same hting and come up with two different reponses.

I assumed he meant that he wanted to be able to read the SQL that makes up the SP... not the results....

If that's the case... the answer is you don't.... at least not directly. I suppose you could use the SP sp_helptext (builtinto SQL Server) and it will return the text of the SP (each line of the SP will be row in the result - of type varchar(255)). But I've never done that and so can't guarantee that it would work

-tg

 

[jmcilhinney]

Now it never even occurred to me that someone would want to do that.

 

[TechGnome]

It's not a common thing to want to do, but I've seen cases where it was nice - mostly inhouse development for getting the current state of a database.

I've never seen a need for it in a production app that goes out.

-tg

 

[Aarvee]

Need to read the contents of SP

Tg has guessed(?) it right. Probably I should have made it clear earlier.

I will explain why I want to read the contents of SP.

Generally when SP has parameters, the way is to keep adding the parameters in VB.Net code and then call the SP through ADO.Net. This would mean two things. Firstly the number of parameters mentioned in SP should match with what is being added in DotNet code. Secondly it should be in proper order. Otherwise we are going to end up with errors.

I felt that in order to avoid such errors, I would like to read the contents of the SP in text form, from where I will be able to extract the parameters portion only and writing a loop in Dotnet wherein I can ensure that the same number of parameters in proper order is added.

It may sound crazy but I wanted to make sure that there is a perfect match between SP and dot net code and I felt that this was the way. If there is a better way, please do enlighten me.

In the absence of knowing how to do it, currently what I am doing is to mark all the SPs in the database ( through enterprise manager) and copy and paste it in one single stroke into notepad and use the notepad text file to do this task. But then there can always be a disconnect between the sp contents and the notepad contents during development phase. Hence the request for a dynamic solution.

I hope the requirement is clear and as I mentioned earlier if there is a better way I would be glad to learn.

Thanks and Best Regards

Varadarajan R

 

[TechGnome]

Sounds like you are trying to build a generic SP runner... don't.... it's more of a pain than it's worth.

If I'm creating the code the first time, I'll copy the parameters and their data types out of the SP and paste it into VB as comments. Then I have all the info I need to create the code for the parameters. I don't do it dynamicaly because if a parameter is changing, the code has to change with it.... be it the creation of the parameters themselves, or the calling sub which now has to pass the data in....

-tg

 

[jmcilhinney]

That's why it didn't occur to me that someone would want to read the actual code of the SP. If you're calling it, surely you know what it's supposed to do. Even if you do read the parameters, that doesn't mean that you know what they represent and what data to assign to them. If you do know what they represent and what data to pass then why do do you need to read them? Unless your DBA is continually switching the order of the parameters, I can't see how you could possibly not know what parameters were required ahead of time. I certainly wouldn't want to be in the position where I was calling SPs I was unfamiliar with.