Get results from VB SQLCRL Stored Procedure

[Chris]

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.

[Erland Sommarskog]

Chris (CL*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx