Suppose you have a stored procedure with some input parameters. There are two methods to know the list of parameters defined in a stored procedure.
Let us create the following stored procedure
CREATE PROCEDURE TEST_PROCEDURE
(
@CUST_ID INT,
@YEAR INT
)
AS
SELECT @CUST_ID,@YEAR
Now to know the parameters used in the TEST_PROCEDURE stored procedure, you can use the following methods
1 Use SP_HELP system stored procedure
EXEC sp_HELP 'TEST_PROCEDURE'
When you execute the above method, this is the result of the second result set.
Parameter_name Type Length Prec Scale Param_order Collation
------------------ ----------- ----------- ----------- ------------------------------
@cust_id int 4 10 0 1 NULL
@year int 4 10 0 2 NULL
2 Use INFORMATION_SCHEMA.PARAMETERS system view
SELECT
PARAMETER_NAME,DATA_TYPE ,ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.PARAMETERS
WHERE
SPECIFIC_NAME='TEST_PROCEDURE'
The result is
PARAMETER_NAME DATA_TYPE ORDINAL_POSITION
------------------------ ------------ ----------------
@cust_id int 1
@year int 2
If you know any other methods feel free to post as a comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Best and more practical to me is double click on SP name (or manually select the proc name) and then ALT + F1. Same for tables.
thanks the trick of management studio shortcuts :)
Matias – That is the trick of management studio shortcuts :)
You can check the parameters used in the XML execution plan under the tag.
For that you need to execute the proc?
I’ve used a query similar to what was shown but have expanded on what is returned. I wrote this a few years ago to analyze the parameters for all report stored procedures.
Select SProcID = O.object_id,
SPName = O.name,
SPCreateDate = O.create_date,
ParmName = Ap.name,
DataTypeDisplay =
Case When t.Name in (‘Decimal’, ‘Numeric’)
Then t.Name + ‘(‘ + cast(Ap.precision as varchar) + ‘,’ + cast(Ap.scale as varchar) + ‘)’
When t.Name in (‘Binary’, ‘VarBinary’,’Char’,’Varchar’,’NChar’,’NVarchar’)
Then t.Name + ‘(‘ + cast(Case When t.Name in (‘nchar’,’nvarchar’)
Then Ap.max_length/2
Else Ap.max_length End as varchar) + ‘)’
Else t.Name End,
DataType = t.Name,
CharacterMaxLen = Case When t.Name in (‘nchar’,’nvarchar’)
Then Ap.max_length/2
Else Ap.max_length End,
NumericPrecision = Ap.precision,
NumericScale = Ap.scale
From sys.objects O
Inner Join
sys.all_parameters Ap ON O.object_id = Ap.object_id
Inner Join
sys.types t ON Ap.system_type_id = t.system_type_id
And Ap.user_type_id = t.user_type_id
Where O.type = ‘P’ And
O.is_ms_shipped = 0
–SP NAME TESTS
AND O.name like ‘rpt%’
–REPORT PARAMETER NAME TESTS
–AND Ap.name = ‘@NetUser’
Order By O.name, Ap.parameter_id
Mike – Thanks for sharing.
sys.parameters – information_schema is so slow…
I agree Grzegorz.
Thanks for this solution.
What about values. For example if I want to save in log table all procedure executes with parameters.
create procedure spInsertData @par1 int, @par2 int
as
begin
declare @paraValues nvarchar(max) = ” –??dynamic get all values
insert into logs (spName, paraValues) values (‘spInsertData ‘, @paraValues)
end
Sir how to get the list of nullable or option parameter of stored procedure in sql server.