Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

call sp with more output params than its definition

5 posts in General Discussion Last posting was on 2011-03-23 14:19:46.0Z
Jose-Miguel Torres Posted on 2011-03-22 15:33:54.0Z
Sender: 3d76.4d88be60.1804289383@sybase.com
From: Jose-Miguel Torres
Newsgroups: sybase.public.ase.general
Subject: call sp with more output params than its definition
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d88c162.3e02.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Mar 2011 07:33:54 -0800
X-Trace: forums-1-dub 1300808034 10.22.241.41 (22 Mar 2011 07:33:54 -0800)
X-Original-Trace: 22 Mar 2011 07:33:54 -0800, 10.22.241.41
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30064
Article PK: 79290

Hello,
Is it possible that when executing a sp , the calling
arguments, specially output params, can be more than its SQL
definition

Example:
---------
Adaptive Server Enterprise/12.5.4/EBF 14065
ESD#3/P/Sun_svr4/OS 5.8/ase1254/2030/64-bit/FBO/Sat Oct 14
07:19:31 2006
(Tested on ASE 15.03 with the same result):

create Procedure amb_ges_calcopagoutili_1
@der_idn int
, @fld_prestaval money
, @der_deduti money
, @fld_prestacod int
, @fld_cant sm
allint
, @fld_copagoisapre char(1)
, @fld_prestacop money Output
, @fld_totcopagoges money Output

as
select "Hola"

begin
declare @der_idn int
declare @fld_prestaval money
declare @der_deduti money
declare @fld_prestacod int
declare @fld_cant smallint
declare @fld_descuentacopagoges char(1)
declare @fld_prestacop money
declare @fld_totcopagoges money
declare @xinspeccion int
declare @extMensajeCAT int
select @der_idn=1
select @fld_prestaval=1
select @der_deduti =1
select @fld_prestacod =1
select @fld_cant=1
select @fld_descuentacopagoges="1"

exec amb_ges_calcopagoutili_1
@der_idn,@fld_prestaval,@der_deduti,
@fld_prestacod,@fld_cant,@fld_descuentacopagoges,
@fld_prestacop output,
@fld_totcopagoges output , @xinspeccion output,
@extMensajeCAT output
end
go
/*** 4 ouput params and sp definitio with 2 out params ***/
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)

----
Hola

(1 row affected)
(return status = 0)

Return parameters:
------------------------------------------------
NULL NULL

Is this a normal behaviour ?

Thank you
JMT


Rob V [ Sybase ] Posted on 2011-03-22 18:37:49.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: call sp with more output params than its definition
References: <4d88c162.3e02.1681692777@sybase.com>
In-Reply-To: <4d88c162.3e02.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d88ec7d$1@forums-1-dub>
Date: 22 Mar 2011 10:37:49 -0800
X-Trace: forums-1-dub 1300819069 10.22.241.152 (22 Mar 2011 10:37:49 -0800)
X-Original-Trace: 22 Mar 2011 10:37:49 -0800, vip152.sybase.com
Lines: 98
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30065
Article PK: 79291


On 22-Mar-2011 16:33, Jose-Miguel Torres wrote:
> Hello,
> Is it possible that when executing a sp , the calling
> arguments, specially output params, can be more than its SQL
> definition
>
> Example:
> ---------
> Adaptive Server Enterprise/12.5.4/EBF 14065
> ESD#3/P/Sun_svr4/OS 5.8/ase1254/2030/64-bit/FBO/Sat Oct 14
> 07:19:31 2006
> (Tested on ASE 15.03 with the same result):
>
> create Procedure amb_ges_calcopagoutili_1
> @der_idn int
> , @fld_prestaval money
> , @der_deduti money
> , @fld_prestacod int
> , @fld_cant sm
> allint
> , @fld_copagoisapre char(1)
> , @fld_prestacop money Output
> , @fld_totcopagoges money Output
>
> as
> select "Hola"
>
> begin
> declare @der_idn int
> declare @fld_prestaval money
> declare @der_deduti money
> declare @fld_prestacod int
> declare @fld_cant smallint
> declare @fld_descuentacopagoges char(1)
> declare @fld_prestacop money
> declare @fld_totcopagoges money
> declare @xinspeccion int
> declare @extMensajeCAT int
> select @der_idn=1
> select @fld_prestaval=1
> select @der_deduti =1
> select @fld_prestacod =1
> select @fld_cant=1
> select @fld_descuentacopagoges="1"
>
> exec amb_ges_calcopagoutili_1
> @der_idn,@fld_prestaval,@der_deduti,
> @fld_prestacod,@fld_cant,@fld_descuentacopagoges,
> @fld_prestacop output,
> @fld_totcopagoges output , @xinspeccion output,
> @extMensajeCAT output
> end
> go
> /*** 4 ouput params and sp definitio with 2 out params ***/
> (1 row affected)
> (1 row affected)
> (1 row affected)
> (1 row affected)
> (1 row affected)
> (1 row affected)
>
> ----
> Hola
>
> (1 row affected)
> (return status = 0)
>
> Return parameters:
> ------------------------------------------------
> NULL NULL
>
> Is this a normal behaviour ?
>
> Thank you
> JMT

When you specify too many parameters for a stored proc, the excess
parameters are simply ignored. I don't know if this is documented but it
has always been this way as long as I can remember.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Jose-Miguel Torres Posted on 2011-03-22 18:42:20.0Z
Sender: 3d76.4d88be60.1804289383@sybase.com
From: Jose-Miguel Torres
Newsgroups: sybase.public.ase.general
Subject: Re: call sp with more output params than its definition
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d88ed8c.43b8.1681692777@sybase.com>
References: <4d88ec7d$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Mar 2011 10:42:20 -0800
X-Trace: forums-1-dub 1300819340 10.22.241.41 (22 Mar 2011 10:42:20 -0800)
X-Original-Trace: 22 Mar 2011 10:42:20 -0800, 10.22.241.41
Lines: 110
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30066
Article PK: 79295

Hello Rob
Thank you. I searched in the manuals but found no reference
at all regarding this specific topic.

Regards
JMT

> On 22-Mar-2011 16:33, Jose-Miguel Torres wrote:
> > Hello,
> > Is it possible that when executing a sp , the calling
> > arguments, specially output params, can be more than its
> > SQL definition
> >
> > Example:
> > ---------
> > Adaptive Server Enterprise/12.5.4/EBF 14065
> > ESD#3/P/Sun_svr4/OS 5.8/ase1254/2030/64-bit/FBO/Sat Oct
> > 14 07:19:31 2006
> > (Tested on ASE 15.03 with the same result):
> >
> > create Procedure amb_ges_calcopagoutili_1
> > @der_idn int
> > , @fld_prestaval money
> > , @der_deduti money
> > , @fld_prestacod int
> > , @fld_cant sm
> > allint
> > , @fld_copagoisapre char(1)
> > , @fld_prestacop money
> > Output , @fld_totcopagoges money
> Output >
> > as
> > select "Hola"
> >
> > begin
> > declare @der_idn int
> > declare @fld_prestaval money
> > declare @der_deduti money
> > declare @fld_prestacod int
> > declare @fld_cant smallint
> > declare @fld_descuentacopagoges char(1)
> > declare @fld_prestacop money
> > declare @fld_totcopagoges money
> > declare @xinspeccion int
> > declare @extMensajeCAT int
> > select @der_idn=1
> > select @fld_prestaval=1
> > select @der_deduti =1
> > select @fld_prestacod =1
> > select @fld_cant=1
> > select @fld_descuentacopagoges="1"
> >
> > exec amb_ges_calcopagoutili_1
> > @der_idn,@fld_prestaval,@der_deduti,
> > @fld_prestacod,@fld_cant,@fld_descuentacopagoges,
> > @fld_prestacop output,
> > @fld_totcopagoges output , @xinspeccion output,
> > @extMensajeCAT output
> > end
> > go
> > /*** 4 ouput params and sp definitio with 2 out params
> > ***/ (1 row affected)
> > (1 row affected)
> > (1 row affected)
> > (1 row affected)
> > (1 row affected)
> > (1 row affected)
> >
> > ----
> > Hola
> >
> > (1 row affected)
> > (return status = 0)
> >
> > Return parameters:
> > ------------------------------------------------
> > NULL NULL
> >
> > Is this a normal behaviour ?
> >
> > Thank you
> > JMT
>
> When you specify too many parameters for a stored proc,
> the excess parameters are simply ignored. I don't know if
> this is documented but it has always been this way as
> long as I can remember.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>


Bret Halford Posted on 2011-03-22 21:18:17.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: call sp with more output params than its definition
References: <4d88ec7d$1@forums-1-dub> <4d88ed8c.43b8.1681692777@sybase.com>
In-Reply-To: <4d88ed8c.43b8.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d891219@forums-1-dub>
Date: 22 Mar 2011 13:18:17 -0800
X-Trace: forums-1-dub 1300828697 10.22.241.152 (22 Mar 2011 13:18:17 -0800)
X-Original-Trace: 22 Mar 2011 13:18:17 -0800, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30067
Article PK: 79296


On 3/22/2011 12:42 PM, Jose-Miguel Torres wrote:
> Hello Rob
> Thank you. I searched in the manuals but found no reference
> at all regarding this specific topic.
>
> Regards
> JMT
>

The behavior is documented here:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36273.1550/html/sprocs/CEGHJJIE.htm


Admittedly, it should be in a place for procedures in general,
rather than just in the sections on system and catalog stored
procedures. I've opened doc CR 663474 to have this behavior
documented in the Reference Manual under EXECUTE.

Note that the behavior can be a bit painful if you are passing
parameters by name, have a parameter that has a default value,
and misspell the parameter name in the execute. The misspelled
parameter is ignored, and the procedure executes using the default.

Cheers,
-bret


Jose-Miguel Torres Posted on 2011-03-23 14:19:46.0Z
Sender: 3d76.4d88be60.1804289383@sybase.com
From: Jose-Miguel Torres
Newsgroups: sybase.public.ase.general
Subject: Re: call sp with more output params than its definition
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d8a0182.5ff6.1681692777@sybase.com>
References: <4d891219@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Mar 2011 06:19:46 -0800
X-Trace: forums-1-dub 1300889986 10.22.241.41 (23 Mar 2011 06:19:46 -0800)
X-Original-Trace: 23 Mar 2011 06:19:46 -0800, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30070
Article PK: 79302


> On 3/22/2011 12:42 PM, Jose-Miguel Torres wrote:
> > Hello Rob
> > Thank you. I searched in the manuals but found no
> > reference at all regarding this specific topic.
> >
> > Regards
> > JMT
> >
>
> The behavior is documented here:
>
>
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36273.1550/html/sprocs/CEGHJJIE.htm
>
>
> Admittedly, it should be in a place for procedures in
> general, rather than just in the sections on system and
> catalog stored procedures. I've opened doc CR 663474 to
> have this behavior documented in the Reference Manual
> under EXECUTE.
>
> Note that the behavior can be a bit painful if you are
> passing parameters by name, have a parameter that has a
> default value, and misspell the parameter name in the
> execute. The misspelled parameter is ignored, and the
> procedure executes using the default.
>
> Cheers,
> -bret

Thank you Bret.
I'll attach this URL to my notes

Regards
JMT