By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Get results from VB SQLCRL Stored Procedure

P: n/a
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.

Jun 7 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jun 7 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.