How to Retrieve a List of Parameters from a Stored Procedure in SQL Server

How to get stored procedure parameters details?

select  
   'Parameter_name' = name,  
   'Type'   = type_name(user_type_id),  
   'Length'   = max_length,  
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
              then precision  
              else OdbcPrec(system_type_id, max_length, precision) end,  
   'Scale'   = OdbcScale(system_type_id, scale),  
   'Param_order'  = parameter_id,  
   'Collation'   = convert(sysname, 
                   case when system_type_id in (35, 99, 167, 175, 231, 239)  
                   then ServerProperty('collation') end)  

  from sys.parameters where object_id = object_id('MySchema.MyProcedure')

How can I retrieve a list of parameters from a stored procedure in SQL Server

You can use SqlCommandBuilder.DeriveParameters() (see SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials) or there's this way which isn't as elegant.

Stored procedure get parameter list and current values

You state '(this code will be executed in the stored procedure itself).' so assuming you are in the procedure you will already know the parameter names as you have to declare them when creating your procedure. Just do a select and put the names inside text fields

ALTER PROCEDURE procname
(
     @param1 NVARCHAR(255)
    ,@param2 INT
    ...
)

SELECT [Parameters] = '@param1=' + @param1 
                    + ',@param2=' + CONVERT(NVARCHAR(MAX),@param2)...

The CONVERT is there as an example for non-char datatypes.

---

update

You will need to create a linked server that points to itself to use the OPENQUERY function.

USE [master]
GO

/****** Object:  LinkedServer [.]    Script Date: 04/03/2013 16:22:13 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'.', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'.', @provstr=N'Integrated Security=SSPI'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'.',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

Now you can do something like this cursor to get each parameter name and then use dynamic sql in OPENQUERY to get the value:

DECLARE curParms CURSOR FOR
SELECT
    name
FROM sys.parameters
WHERE OBJECT_ID = OBJECT_ID('schema.procedurename')
ORDER BY parameter_id
OPEN curParms
FETCH curParms INTO @parmName
WHILE @@FETCH_STATUS <> -1
BEGIN
    SELECT @parmName + '=' + (SELECT * FROM OPENQUERY('linkedservername','SELECT ' + @parmName))
    FETCH curParms INTO @parmName
END
CLOSE curParms
DEALLOCATE curParms

How do I get the list of all stored procedures and their parameters starting with a certain prefix?

To get information on the stored procedures:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES 

To find the sprocs starting with a certain prefix (e.g. "usp"):

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'usp%'

To find all the parameters for a stored procedure:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='YourSprocName'

To find all the parameters for all stored procedures starting with a certain prefix:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME LIKE 'usp%'

Use a stored procedure to get a list of nvarchar(20) values as a parameter

you can use a temporary table to recuperate your list from the stored procedure, like the example below :

create proc Test
AS BEGIN

SELECT CAST('jkj' AS NVARCHAR(20)) value 

END

DECLARE @tmp TABLE(value nvarchar(20))

INSERT INTO @tmp EXEC GhaziTest

SELECT * from @tmp

C# SQL Server stored procedure parameter return list

Console.WriteLine(output); will simply display the result of ToString on the parameter, which for this type reverts to the default implementation, which just shows the name of the type of the output object. You need to iterate through the items in the list using foreach:

foreach (var item in output)
{
    Console.WriteLine(item);
}

Which would cause you to get a row of lines just giving the type of that object. So either you can write an implementation of ToString for your object, or simply display the bits you want to see...

foreach (var item in output)
{
    Console.WriteLine($"{item.ItemName} {item.Quantity}");
}

Dynamically Retrieve Parameter Names & Current Values Inside T-SQL Stored Procedure

You don't have to dynamically retrieve names and parameters inside the stored proc, because of once the stored proc is created or altered it cannot change its parameters until it is altered or recreated again.

Instead, you may have the list of parameters inside the stored proc static, but to not enum params manually you can have it generated dynamically by DDL trigger.

Define some comment-tags, that will be used to mark the place, where the parameters should be listed within the stored proc and add them to the body of the stored proc, where appropriate. The trigger should find markers and alter proc inserting static list of parameter names and their values between the markers. The example follows.

DDL-trigger

create trigger StoredProc_ListParams on database
for CREATE_PROCEDURE, ALTER_PROCEDURE
as
begin
    set nocount on;

    if @@nestlevel > 1
        return;

    declare @evt xml, @sch sysname, @obj sysname, @text nvarchar(max);

    set @evt = eventdata();
    set @text = @evt.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]', 'nvarchar(max)');

    if @text is NULL -- skip encrypted
        return;

    set @sch = @evt.value('(/EVENT_INSTANCE/SchemaName/text())[1]', 'sysname');
    set @obj = @evt.value('(/EVENT_INSTANCE/ObjectNa1me/text())[1]', 'sysname');

    declare @listParams nvarchar(max);

    set @listParams = '
    select name, value
    from (values ' + stuff(
        (select ',
        ' + '(' + cast(p.parameter_id as varchar(10)) + ', ''' + p.name + '''' +
        ', cast(' + p.name + ' as sql_variant))'
    from sys.parameters p
        join sys.objects o on o.object_id = p.object_id and o.type = 'P'
        join sys.schemas s on s.schema_id = o.schema_id
    where s.name = @sch and o.name = @obj
    order by p.parameter_id
    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 1, '') + '
        ) p(num, name, value)
    order by num';

    declare @startMarker nvarchar(100), @endMarker nvarchar(100);
    set @startMarker = '--%%LIST_PARAMS_START%%';
    set @endMarker = '--%%LIST_PARAMS_END%%';

    if left(@text, 6) = 'create'
        set @text = stuff(@text, 1, 6, 'alter');

    declare @ixStart int, @ixEnd int;
    set @ixStart = nullif(charindex(@startMarker, @text), 0) + len(@startMarker);
    set @ixEnd = nullif(charindex(@endMarker, @text), 0);

    if @ixStart is NULL or @ixEnd is NULL
        return;

    set @text = stuff(@text, @ixStart, @ixEnd - @ixStart, @listParams + char(13) + char(10));

    if @text is NULL
        return;

    exec(@text);
end

The script of the stored proc for the test:

create procedure dbo.TestProc
(
    @id int,
    @name varchar(20),
    @someFlag bit,
    @someDate datetime
)
as
begin
    set nocount on;

--%%LIST_PARAMS_START%%
    -- list params for me here, please
--%%LIST_PARAMS_END%%

end

And the following is how the stored proc actually looks in the database once the above create script is executed:

alter procedure [dbo].[Test]
(
    @id int,
    @name varchar(20),
    @someFlag bit,
    @someDate datetime
)
as
begin
    set nocount on;

--%%LIST_PARAMS_START%%
    select name, value
    from (values 
        (1, '@id', cast(@id as sql_variant)),
        (2, '@name', cast(@name as sql_variant)),
        (3, '@someFlag', cast(@someFlag as sql_variant)),
        (4, '@someDate', cast(@someDate as sql_variant))
        ) p(num, name, value)
    order by num
--%%LIST_PARAMS_END%%

end

The one restriction with this approach is that it will not work with encrypted stored procs. Also you will have to do some adjustments, if you wish to handle parameters of the table type.

---

Related Topics