user stored proc parameter finder

Question

Post reply

user stored proc parameter finder

sureshj

Valued Member

Points: 74
More actions
January 4, 2008 at 8:37 am

#185150

Hi,
Can anyone help on how to find user stored procs parameter with their names, datatype, field size etc. Is there any system stored proc that takes user stored proc name and can be executed to know this info? Any help will be greatly appreciated.
Thanks
suresh

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

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

WHERE SPECIFIC_NAME = 'myproc'

That'll get you exactly what you want

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sureshj Valued Member Points: 74 More actions · Answer 2

Thanks a lot Grant,

This is exactly what I was looking for. Another quick question on the same topic. Is there any system stored proc that provide the same information? Also when I used your select stmt, if the field is using varchar(max) the query returns -1 for the character maxlength column. Is there any way to overcome this situation?

Thanks again for your help.

suresh

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

This isn't documented, but does what you want:

exec sp_procedure_params_rowset 'procname'

The -1 returned by the view is the designed method. The procedure, in my mind, is worse. It shows a length of 1073741823. Less helpful and less accurate if you ask me.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sureshj Valued Member Points: 74 More actions · Answer 4

Hi Grant,

Thanks again. Then is their anyway to find the actual field size of the Int column?. In my Table the column properties displays as size = 4 for the column "Age" which is a int datatype. But the query returns -1.

thanks

suresh

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

I'm not sure what you mean. We're not talking about tables, but about stored procedures. If you have a data type of int in the stored procedure, the character length from the proc and the query both return as NULL.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sureshj Valued Member Points: 74 More actions · Answer 6

Hi Grant,

Sorry I meant Stored Proc. But I got the solution. Yes as you said it does return just NULL. Thanks a lot for your help.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Two way street here... please share your solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sureshj Valued Member Points: 74 More actions · Answer 8

Hi Jeff,

What I meant was that I understood Grant's solution. And it does return NULL when you execute the stored proc given by Grant, but it returns -1 if you execute the query.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

Ok, thanks for the feedback... does that mean that you're all set?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sureshj Valued Member Points: 74 More actions · Answer 10

Hi Jeff,

Thanks. I am all set for this task. But I am trying a new task in SQL Server 2005, with regards to SSIS. Can I ask you a quick question on that? Kindly please advice

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

I'd start a new thread with a new subject... will probably get more attention that way...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sureshj Valued Member Points: 74 More actions · Answer 12

sureshj

Valued Member

Points: 74

January 4, 2008 at 10:08 pm

#765049

Thanks Jeff,

I will do that.