Stored Procedure Output Parameter Woes

30 Dec 2005 10:19 PM

Denis

I have written a stored procedure with several output parameters.

eg
@Vendor bit OUTPUT

When I execute this stored procedure via query analyzer it thinks I have
declared this as input/output. Is this a feature or is there something
fundamental I have missed?

I am attempting to call this stored procedure from VB.net 2003 so I find I
am declaring the direction as OUTPUT but then I have to supply a value which
I find a little perplexing if not contradictory. Any comments anyone?

--
Denis

30 Dec 2005 10:35 PM

Erland Sommarskog

Denis (De***@discussions.microsoft.com) writes:

> I have written a stored procedure with several output parameters.
>
> eg
> @Vendor bit OUTPUT
>
> When I execute this stored procedure via query analyzer it thinks I have
> declared this as input/output. Is this a feature or is there something
> fundamental I have missed?

Hey, this is T-SQL, not Ada! It's not that sophisticated that it has
output-only parameters. T-SQL has input and input-output parameters.

> I am attempting to call this stored procedure from VB.net 2003 so I find
> I am declaring the direction as OUTPUT but then I have to supply a value
> which I find a little perplexing if not contradictory. Any comments
> anyone?

Not really sure what you mean, but if the parameter does not have a
default value, you will have to specify it.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2 Jan 2006 10:17 AM

Denis

Erland

This may not be Ada, PL/I, Algol, Simula, C or any number of other languages
but it seems to me if I am able to declare something as OUTPUT in T-SQL then
it should be output not input/output.

Your response to my second question has removed the necessity to supply an
'input' value.

Thanks

--
Denis

Show quote

"Erland Sommarskog" wrote:

> Denis (De***@discussions.microsoft.com) writes:
> > I have written a stored procedure with several output parameters.
> >
> > eg
> > @Vendor bit OUTPUT
> >
> > When I execute this stored procedure via query analyzer it thinks I have
> > declared this as input/output. Is this a feature or is there something
> > fundamental I have missed?
>
> Hey, this is T-SQL, not Ada! It's not that sophisticated that it has
> output-only parameters. T-SQL has input and input-output parameters.
>
> > I am attempting to call this stored procedure from VB.net 2003 so I find
> > I am declaring the direction as OUTPUT but then I have to supply a value
> > which I find a little perplexing if not contradictory. Any comments
> > anyone?
>
> Not really sure what you mean, but if the parameter does not have a
> default value, you will have to specify it.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

2 Jan 2006 12:17 PM

Erland Sommarskog

Denis (De***@discussions.microsoft.com) writes:

> This may not be Ada, PL/I, Algol, Simula, C or any number of other
> languages but it seems to me if I am able to declare something as OUTPUT
> in T-SQL then it should be output not input/output.

Nah, computer languages are what they are, and a keyword can mean one
thing in one language, and other thing in another language.

It's about 20 years too late to complain on the choice of OUTPUT as a
keyword for input/output parameters. I guess the reasoning was that
input was always implied, so there was no need to declare this. As far
as I know, Ada was the first language to have output-only parameters.
When Sybase saw the light of day, Ada had been around for a while. However,
many with a C background had a very negative view of Ada, and thus
failed to see many of the good ideas in the language.

Anyway, output-only or not, as long as the parameter is not optional, you
will always have to supply it. What output-only means in a strong 3GL is
that 1) you cannot supply a constant 2) what ever value the variable when
procedure is called, the called procedure will not see the value.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx