Hello everyone,

Say I have a stored proc with a parameter that takes a null value and a user has a typo in the parameter. Is there any way in Sybase 12.x at the stored procedure level to catch this mistake. As far as I know, Sybase just ignores spurious params.

Here's a simple example :

create procedure myProc
(@theId numeric(12),
@updateTS datetime=null,
@debug tinyint=0
)
as
if @updateTS is null
begin
select * from MyTable where TheId = @theId
end
else
begin
select * from MyTable where TheId = @theId and UPD_TS < @updateTS
end
go

For instance, the user types:
exec myProc @theId = "123", @updateTime = "20030101"
and is perplexed by results.

I can't redesign the proc. It's in wide use and the procedure is well-documented. The real example is more complex. (It uses dynamic sql, has about 20 params with most of the params defaulting to null. )

Thanks for your help,
Grandpa

Not possible to change this behavior. In fact, the SP returns what you're asking for...

I agree, a msg like "error : parameter @xxx doesn't exist for the procedure sp_xxx" will be welcome

fadace,

Thanks for the confirmation.

Could I ask you one other question? Are the defaults for a stored procedure's parameters stored anywhere in the database (except for sp_helptext). I see in 12.5, sp_help indicates if a param is "in or out" but I can't find the defaults listed anywhere. One of my colleagues is putting something together that assesses the params to a stored procedure dynamically and wants to avoid parsing sp_helptext.

Thanks,
Grandpa

There may be some representation in syscolumns.

There is a column syscolumns.cdefault that contains the ID of the procedure that generates the default value for the column. Since syscolumns stores parameters to stored procedures as well as columns in tables and views, the cdefault column may be used to determin the default. If so it's like a default is created for each parameter that has a default value.

What I'm say may happen, or I may be completely wrong. I haven't checked it out.

Richard.