Stored procedure unknown arguments

#1

How can I get Sybase to print a warning or error when an unknown
argument is given to a stored procedure?

Quote:

create procedure foo @a int as print "hello"
go

foo @a = 44
go
hello

(return status = 0)

Quote:

foo
go
Msg 201, Level 16, State 2

Server 'TEST_LN', Procedure 'foo'
Procedure foo expects parameter @a, which was not supplied.
(return status = -6)

Okay so far - if I leave out the parameter @a then it gives an error.
But if I specify a nonexistent parameter it silently ignores it:

Quote:

foo @a = 44, @b = 44
go
hello

(return status = 0)

Can I make Sybase warn about bad parameters? I'm using 11.9.2 with
OCS 12.5.

--
Ed Avis <ed (AT) membled (DOT) com>

#2

On Wed, 12 May 2004 09:24:50 +0100, Ed Avis wrote:

Quote:

How can I get Sybase to print a warning or error when an unknown argument
is given to a stored procedure?

Okay so far - if I leave out the parameter @a then it gives an error. But
if I specify a nonexistent parameter it silently ignores it:

foo @a = 44, @b = 44
go
hello
(return status = 0)

Can I make Sybase warn about bad parameters? I'm using 11.9.2 with OCS
12.5.

You can't (AFAIK). Sybase will simply silently ignore any additional
parameters that are supplied.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

#3

Add on an additional argument with a default of NULL and check it at the
beginning of your code. I had an sp_add num1, .....[num12]]]]]] proc which
would add and comma-format the sum. If someone ever used it with more than
12 arguments they would get an incorrect result. So I used this technique
of first checking that the 13th numeric argument was NULL.

"Ed Avis" <ed (AT) membled (DOT) com> wrote

Quote:

How can I get Sybase to print a warning or error when an unknown
argument is given to a stored procedure?

create procedure foo @a int as print "hello"
go

foo @a = 44
go
hello
(return status = 0)

foo
go
Msg 201, Level 16, State 2
Server 'TEST_LN', Procedure 'foo'
Procedure foo expects parameter @a, which was not supplied.
(return status = -6)

Okay so far - if I leave out the parameter @a then it gives an error.
But if I specify a nonexistent parameter it silently ignores it:

foo @a = 44, @b = 44
go
hello
(return status = 0)

Can I make Sybase warn about bad parameters? I'm using 11.9.2 with
OCS 12.5.

--
Ed Avis <ed (AT) membled (DOT) com

#4

Michael Peppler wrote:

Quote:

You can't (AFAIK). Sybase will simply silently ignore any additional
parameters that are supplied.

You can kludge it. For example, if your proc expects only
one parameter:

create procedure sp_myproc
@param1 varchar(255),
@param2 varchar(255) = null
as
if @param2 is not null
begin
print "Too many parameters"
return -1
end

-- etc...
return

#5

On Wed, 12 May 2004 10:36:55 -0700, Larry Coon wrote:

Quote:

Michael Peppler wrote:

You can't (AFAIK). Sybase will simply silently ignore any additional
parameters that are supplied.

You can kludge it. For example, if your proc expects only one parameter:

create procedure sp_myproc
@param1 varchar(255),
@param2 varchar(255) = null
as
if @param2 is not null
begin
print "Too many parameters"
return -1
end

But that doesn't work with named parameters.

I can call the above with

exec sp_myproc @param1 = 'foo', @bar = 'baz'

and not get an error...

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html