GP Developer Tip: List All Parameters for a SQL Server Stored Procedure

Occasionally I work on a Dynamics GP integration where I have to call a Dynamics GP stored procedure from a .NET application.  As always, there are several ways to do this, but I typically use one of two approaches.

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[29];
sqlParameters[0] = new SqlParameter("@EMPLOYID", System.Data.SqlDbType.VarChar, 15);
sqlParameters[1] = new SqlParameter("@ID_Accrual_Code", System.Data.SqlDbType.VarChar, 11);
sqlParameters[2] = 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:

sp_help zDP_PM10200SI

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.

http://www.precipioservices.com