Need to get Procedure default values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need to get Procedure default values

Author

Topic

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-28 : 07:54:26

Hi All,

I need to get the default vaules given to the Parameters of a StoredProcedure using TSQL.

for ex :
CREATE PROC dbo.usp_MyModule_User_Insert
(
@userName VARCHAR(100) = 'JOHN',
@city VARCHAR(10) = 'CA'
)
AS
.....

Now i need to get the values as
ParamName value
----------------
@userName 'JOHN'
@city 'CA'

Thanks in advance.

Peter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-28 : 08:19:25

Why?
The parameters will have the correct values of no value is given.

Peter Larsson
Helsingborg, Sweden

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-28 : 08:25:18

Hi Peso,

We have an internal procedure testing application under development.
We are developing it to test a procedure automatically with all possible in put combinations,
for that we have to maintain the ProcName, Paramaeters, datatype, tables used in it and
defaults.

That's y i need to get the values.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-28 : 08:30:30

Try

select * from sys.all_parameters

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 08:37:41

There is no direct way to get this. The only way to find parameter default value is to parse the procedure text as shown here:

[url]http://education.sqlfarms.com/education/ShowPost.aspx?PostID=277[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources