CODEWITHSUNDEEP
sqlvariablesstored-procedures
Alfredo Matute
Alfredo Matute

Reputation: 193

Select stored procedure variables

Is there a way to select the variables a stored procedure will receive?

For example I have a table of clients, my primary key is an autonumeric long.

My stored procedure is called SelectClientByKey and would be something like 

select * 
from clients 
where clientId = @clientid

I have many other stored procedures, and I would like to make a query that gets the variables that the stored procedure has and it's data types.

Something like:

Select Variable, Type 
From StoredProcedure 
Where StoredProcedure.Name = 'SelectClientByKey'

The results should be 

@clientId bigInt

I'm not sure if that's possible, any advice?

Upvotes: 0

Views: 868

Answers (2)

Dhana
Dhana

Reputation: 1658

It is possible. You can use below SQL query to get stored procedure, parameter list and its datatype:

SELECT 
    o.name as StoredProcedure, p.name as Parameter, t.name as DataType
FROM 
    sys.all_objects o
JOIN
    sys.parameters p ON p.object_id = o.object_id
JOIN
    sys.types t ON t.system_type_id = p.system_type_id
WHERE 
    o.name = 'SelectClientByKey'    -- Your stored procedure name

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Shamelessly stolen from here. If you run it, it will given you a list of all stored procedures and their parameters as well as some useful extended information.

SELECT
   SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
   SO.name AS [ObjectName],
   SO.Type_Desc AS [ObjectType (UDF/SP)],
   P.parameter_id AS [ParameterID],
   P.name AS [ParameterName],
   TYPE_NAME(P.user_type_id) AS [ParameterDataType],
   P.max_length AS [ParameterMaxBytes],
   P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P 
   ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN (
   SELECT OBJECT_ID 
   FROM sys.objects
   WHERE TYPE IN ('P','FN')
)
ORDER BY [Schema], SO.name, P.parameter_id
GO

Upvotes: 2

Related Questions