In situations where the procedure returns one or more values in OUTPUT parameters, I usually like to call the stored procedure directly in a procedure (rather than rely on a separate call to a data access class). In this case, I use the very, very cool DeriveParameters method:
gpCommand = sqlConn.CreateCommand();
gpCommand.CommandType = CommandType.StoredProcedure;
gpCommand.CommandText = "zDP_PM10200SI";
//Get all of the parameters from the stored procedureSystem.Data.SqlClient.SqlCommandBuilder.DeriveParameters(gpCommand);
When you call DeriveParameters, .NET interrogates the stored procedure and builds a list of all of the parameter objects for you. This saves ALOT of typing, and ensures that all of the parameters are properly declared.
On the other hand, when I don't need to get return values from a stored procedure, I use my data access class, which requires an array of SqlParameter objects that includes the parameter name and data type. In this case, I have to assemble the array and assign the SqlParameters.
SqlParameter sqlParameters = new SqlParameter;
sqlParameters = new SqlParameter("@EMPLOYID", System.Data.SqlDbType.VarChar, 15);
sqlParameters = new SqlParameter("@ID_Accrual_Code", System.Data.SqlDbType.VarChar, 11);
sqlParameters = new SqlParameter("@ID_Accrual_Code_2", System.Data.SqlDbType.VarChar, 11);
In this case I need the parameter name and data type. Some stored procedures are simple and only have a few parameters. But there are a few that have dozens of parameters, which makes this type of coding a tedious chore.
So make this process easier, I wanted to get a list of all of the stored procedure parameters.
It turns out that this is pretty easy--just use sp_help:
This provides a very nice parameter listing, with data types, that can be copied and pasted into Excel or text editor, and then easily converted into code.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles. He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.