If you want to see what parameters a stored procedure is using then you can accomplish this in a couple of different ways. You can use sys.parameters or INFORMATION_SCHEMA.parameters from within SQL Server itself, you can also SqlCommandBuilder from within ADO.NET

Let’s say you have a procedure name prTest in a database named Test2

T-SQL
1
2
3
4
CREATE PROCEDURE prTest
@id int,@SomeDate date,@Somechar CHAR(1) OUTPUT
AS
SELECT GETDATE(),@id ,@SomeDate ,@Somechar
CREATE PROCEDURE prTest
@id int,@SomeDate date,@Somechar CHAR(1) OUTPUT
AS
SELECT GETDATE(),@id ,@SomeDate ,@Somechar

Here is how you would do it with c# and ADO.NET

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using System;
using System.Data;
using System.Data.SqlClient;
 
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string yourConnStr = "Data Source=localhost;Initial Catalog=Test2;Integrated Security=SSPI;";
            using (SqlConnection sqlConn = new SqlConnection(yourConnStr))
            using (SqlCommand sqlCmd = new SqlCommand("prTest", sqlConn))
            {
                sqlConn.Open();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(sqlCmd);
                Console.WriteLine(sqlCmd.Parameters.Count.ToString());
                    foreach (SqlParameter p in sqlCmd.Parameters) {
 
                        Console.WriteLine(p.ParameterName.ToString() + "t" 
                                + p.Direction.ToString() + "t" + p.DbType.ToString() );
                        }
                Console.ReadLine();
               
                
        }
    }
}
using System;
using System.Data;
using System.Data.SqlClient;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string yourConnStr = "Data Source=localhost;Initial Catalog=Test2;Integrated Security=SSPI;";
            using (SqlConnection sqlConn = new SqlConnection(yourConnStr))
            using (SqlCommand sqlCmd = new SqlCommand("prTest", sqlConn))
            {
                sqlConn.Open();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(sqlCmd);
                Console.WriteLine(sqlCmd.Parameters.Count.ToString());
                    foreach (SqlParameter p in sqlCmd.Parameters) {

                        Console.WriteLine(p.ParameterName.ToString() + "t" 
                                + p.Direction.ToString() + "t" + p.DbType.ToString() );
                        }
                Console.ReadLine();
               
                
        }
    }
}

Output

4
@RETURN_VALUE   ReturnValue     Int32
@id             Input           Int32
@SomeDate       Input           Date
@Somechar       Input           AnsiStringFixedLength

As you can see, we get 4 parameters back not 3, this is because the return value is also included. sqlCmd.Parameters.Count returns 4 and when we loop over the sqlCmd.Parameters collection we are displaying the name, direction and data type of the parameter

If you want to do something similar from within SQL Server, you can use the INFORMATION_SCHEMA.parameters view

T-SQL
1
2
3
SELECT parameter_name, ordinal_position,parameter_mode,data_type 
FROM INFORMATION_SCHEMA.parameters
WHERE SPECIFIC_NAME = 'prTest'
SELECT parameter_name, ordinal_position,parameter_mode,data_type 
FROM INFORMATION_SCHEMA.parameters
WHERE SPECIFIC_NAME = 'prTest'

Here is the output

parameter_name ordinal_position parameter_mode data_type
@id 1 IN int
@SomeDate 2 IN date
@Somechar 3 INOUT char

As you can see INFORMATION_SCHEMA.parameters does not return a row for the return value.

You can also use sys.parameters and join that with sys.types

T-SQL
1
2
3
4
5
6
7
SELECT s.name AS parameter_name,
       parameter_id AS ordinal_position,
       CASE is_output WHEN 0 THEN 'IN' ELSE 'INOUT' END Parameter_Mode,
       t.name AS data_type 
FROM sys.parameters s
JOIN sys.types t ON s.system_type_id = t.user_type_id
WHERE object_id = object_id('prTest')
SELECT s.name AS parameter_name,
	   parameter_id AS ordinal_position,
	   CASE is_output WHEN 0 THEN 'IN' ELSE 'INOUT' END Parameter_Mode,
	   t.name AS data_type 
FROM sys.parameters s
JOIN sys.types t ON s.system_type_id = t.user_type_id
WHERE object_id = object_id('prTest')

Here is the same output as with INFORMATION_SCHEMA.parameters

parameter_name ordinal_position parameter_mode data_type
@id 1 IN int
@SomeDate 2 IN date
@Somechar 3 INOUT char

I think INFORMATION_SCHEMA.parameters is a little easier to use, another benefit is that INFORMATION_SCHEMA.parameters also exists on other platforms