I'm trying to figure out the inputs and the outputs of any SQL Server 2008 stored procedure.
I'm using SMO to connect to a database instance, then I'm grabbing all of the databases and each of the databases stored procedures.
Currently, I can take the StoredProcedure object and get the input parameters, but I'm having trouble finding the result.
I know that I can use ExecuteWithResults to pass in my stored procedure to run it, but how can I get the results?
Also, how do I pass in the parameters? Do I build an exec command and pass that in?
Edit: I have found that I need to use SET FMTONLY ON, but how exactly do I pass that in to ExecuteWithResults?
Figured it out. Use StringCollection to build a collection of strings. Make sure to Add("SET FMTONLY ON") so you are not affecting any real data. Build an exec command with all of your params from the stored procedures Parameters collection, then executeWithResults on that collection. This will return a DataSet[]. Grab the same index in your dataset array that you added your exec command on your StringCollection. From here, you can get the results table set, then its columns, that contains all of the column names and their datatypes.