Posted by Erland Sommarskog on 06/07/06 22:11

Chris (CLarkou@gmail.com) writes:
> When calling this stored procedure from a TSQL stored procedure for
> using the value for further processing the value returned to my
> variable is 0. The correct value should be 56. In results tab I get the
> correct result, but how can I assign it to my variable @max ?
>
> DECLARE @max1 int
> DECLARE @max int
>
> EXEC @max1 = [dbo].[VBSTP_calculate_MAX_no]
> @vsp_table_name = N'[dbo].[Message]',
> @vsp_table_key = N'message_no',
> @vsp_WHERE = N''
>
> print @max1 -- value here is 0

A stored procedure (no matter if it's written in T-SQL or VB .Net) can
return values in three different ways:

1) Return value.
2) Output parameters.
3) Result set.

Your procedure returns a result set, but above you are retrieving the
return value. In my opinion, return values should be used to indicate
success/failure (with 0 meaning success) and nothing else.

If the purpose of your VB procedure is to compute a single value, you
should not return a result set from it, but you should return an output
parameter. Or maybe even better - you should make it a function.


--
Erland Sommarskog, SQL Server MVP, esquel@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

[Back to original message]