How to get storedprocedures parameters with types

Question

How to get storedprocedures parameters with types

DexterForALL1986

SSC Journeyman

Points: 78
More actions
June 1, 2011 at 5:57 am

#238512

Hello All,
I would like to know how to get the stored procedures
parameters along with it's types ??
create proc spstore
as
@ID INt,
@EmpName varchar(25)
Begin
insert into (-----)
ENd
I want to retrive the parameters ,Type
ie 1> @ID Int
2> @EmpName varchar(25)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

They should be in sys.columns. Query sys.procedures join sys.columns

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95461 More actions · Answer 2

Actually, you need to query sys.parameters...

SELECT SchemaName = ss.name,

ObjectName = so.name,

ParameterName = sp.name,

DataTypeName = st.name + CASE WHEN st.name LIKE '%char'

THEN '(' + CASE WHEN sp.max_length = -1

THEN 'max'

ELSE CONVERT(VARCHAR(10), sp.max_length)

END + ')'

WHEN st.name IN ('decimal','numeric')

THEN '(' + CONVERT(VARCHAR(10), sp.scale) + ',' + CONVERT(VARCHAR(10), sp.precision) + ')'

ELSE ''

END,

sp.scale,

sp.precision,

sp.parameter_id, sp.*

FROM sys.parameters sp

JOIN sys.objects so

ON sp.object_id = so.object_id

JOIN sys.schemas ss

ON so.schema_id = ss.schema_id

JOIN sys.types st

ON sp.user_type_id = st.user_type_id

ORDER BY SchemaName, ObjectName, sp.parameter_id;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Or you could keep it ISO, and avoid all those joins, with INFORMATION_SCHEMA.PARAMETERS.

John

DexterForALL1986 SSC Journeyman Points: 78 More actions · Answer 4

DexterForALL1986

SSC Journeyman

Points: 78

June 1, 2011 at 11:37 pm

#1332914

Thank you very much it had solved my issue

ravigujarati222 SSCommitted Points: 1708 More actions · Answer 5

ravigujarati222

SSCommitted

Points: 1708

June 3, 2011 at 12:05 am

#1333515

Please use this.

SP_HELP 'Procedure Name'

ravigujarati222 SSCommitted Points: 1708 More actions · Answer 6

tharuraju (6/1/2011)
Hello All,
I would like to know how to get the stored procedures
parameters along with it's types ??
create proc spstore
as
@ID INt,
@EmpName varchar(25)
Begin
insert into (-----)
ENd
I want to retrive the parameters ,Type
ie 1> @ID Int
2> @EmpName varchar(25)

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

Whats wrong with using sp_help 'proc name' ?

Jayanth Kurup[/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Jayanth_Kurup (6/15/2011)
Whats wrong with using sp_help 'proc name' ?

It returns more than one result set so it's difficult to use programatically. If you compare the SQL behind the view 'INFORMATION_SCHEMA.PARAMETERS' and the SQL executed by sp_help to return the parameters, I'd wager that it's very similar.

John

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 9

Aahh Yes , i didnt think that the data would be used elsewhere programmatically ;D

Jayanth Kurup[/url]