Get results from VB SQLCRL Stored Procedure

Hi,

I created a VB SQL CRL Stored procedure for calculating a value. Value
is returned as below

Using sConn4 As New SqlConnection("context connection=true")
sConn4.Open()
scmd = New SqlCommand("SELECT " & var_max, sConn4)
sdrd = scmd.ExecuteReader()
SqlContext.Pipe.Send(sdrd)
End Using

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

SET @.max = (SELECT @.max1)

Thanks a lot.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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx