Parameter defaults in stored procedures

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)

Parameter defaults in stored procedures

Author

Topic

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-10-23 : 12:24:50

How would one query the default value a parameter retrieves inside a stored procedure?

For example:

create proc foo
@somevar = 'bar'
as
print @somevar
go

I want to return a resultset that shows @somevar has a default value of 'bar'. Any help would be appreciated.

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-10-23 : 12:29:06

SELECT @somevar

===========
Paul

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-10-23 : 12:39:02

Let me clarify....

I want to interogate N number of procedures, and I am looking for a way to determine the parameter_default using an information_schema.view or directly from the system tables.

In the example below I would like to figure out how to return that
@OrdYear has a default value of '1998'. Please help me populate parameter_default with this value.

sp_helptext SalesByCategory

use northwind

sp_helptext SalesByCategory

select
parameter_name
, null as parameter_default
from
information_schema.parameters
where
specific_name = 'SalesByCategory'
order by
ordinal_position

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-23 : 14:22:40

It seems the only place to get this is syscomments ... here is a sample of how you might go about that ...


drop table #params
drop proc test
go
create proc test
    @x int = 1,
    @y int = 2
as
select 'blah'
go

declare @csv varchar(8000), @sep char(1)

select
    @sep = ',',
    @csv = 
    replace(replace(
    substring(sc.text,
        charindex('@',sc.text),
        charindex('as',sc.text) - charindex('@',sc.text)),char(10),''),char(13),'')
from
    dbo.sysobjects so
    inner join dbo.syscomments sc
        on so.id = sc.id
where
    so.name = 'test' and
    so.xtype = 'p'

select 
    ltrim(rtrim(nullif(substring(@sep+@csv+@sep,n,charindex(@sep,@sep+@csv+@sep,n)-n),''))) as word
into
    #params
from
    numbers --this is a tally table: create table numbers(n int) -> fill with 1 to n sequential positive ints ....
where
    n<=len(@sep+@csv+@sep) and
    substring(@sep+@csv+@sep,n-datalength(@sep),datalength(@sep))=@sep and
    charindex(@sep,@sep+@csv+@sep,n)-n>0

select
    left(word,charindex(' ',word)) as parameter,
    right(word,datalength(word) - charindex('=',word) - 1) as [default]
from
    #params

EDIT:

this cracks me up!!! I find this solution very very funny for some reason ...

Jay White
{0}

Edited by - Page47 on 10/23/2002 14:26:16

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-10-23 : 16:29:28

That's a beaut!

Thank You!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources