Procedure param 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)

Procedure param default values

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-05-24 : 11:17:53

Heya,

I know I can pull the parameter names from SQL system tables (see TSQL below).

Is there a table that stores the default values as well (I'd rather not strip them out of SYSCOMMENTS)?

Thanks!

DECLARE @PROCNAME VARCHAR(256)
SET @PROCNAME = 'STUFF'

SELECT
@PROCNAME
, A.NAME
, B.NAME
, B.LENGTH
, B.ALLOWNULLS
, A.ID
FROM SYSCOLUMNS A
JOIN
SYSTYPES B
ON A.XTYPE = B.XTYPE
WHERE A.ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = @PROCNAME)

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-25 : 01:41:35

check

information_schema.parameters and
information_schema.columns

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-25 : 04:54:50

from BOL -- syscolumns

cdefault :-- ID of the default for this column.
check the column values.

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-05-29 : 14:40:33

Thanks, but these tables don't appear to store the default parameter values. For example, in the following proc, I'm looking for the table that lists the default value for @stuff ('rock on!')

create proc rock
(@stuff varchar(128) = 'rock on!'
)
as
...

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-31 : 00:18:51

Hi pug,

check the URL http://education.sqlfarms.com/education/ShowPost.aspx?PostID=277
it consists of a function to get the defaults.

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 10:28:52

Rock, this what I feared, but since they've coded it up already, not so bad.
Thanks PeterNeo!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources