Calling SQL 2005 stored procedure that returns a value

I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.

I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).

I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.

I am calling the SP as follows:

Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val)
Dim result As String
Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "CreateLogEntry"
cmd.Parameters.Add("@.ChID", Data.SqlDbType.Int)
cmd.Parameters("@.ChID").Value = ID
cmd.Parameters.Add("@.Value", Data.SqlDbType.Int)
cmd.Parameters("@.Value").Value = val
result = cmd.ExecuteNonQuery().ToString
End Using
End Sub

I have tried amending the ExecuteNonQuery line to ExecuteReader()

Any help appreciated

Regards

Clive

Greetings Clive

I would have made the stored procedure use an output parameter as more outputs can always be added. A return can only return one value!

So as output you would add the lines:

cmd.Parameters.Add("Fred"), Data.SqlDbType.Int)

cmd.Parameters("Fred").Direction = Output;

after the result add

Dim iResult As Integer = md.Parameters("Fred").Value

|||

Thanks for that - you are right the ability to return more than one value would be useful.

At the moment in my T-SQL I use the RETURN construct to generate the return value. How would I change that to use an output parameter instead?

regards

Clive

|||

As in

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clive Chinery
-- Create date: 13Aug2007
-- Description: Test for records for today and tomorrow
-- =============================================
CREATE PROCEDURE dbo.DoDateTest
@.Test1 BIT OUTPUT,
@.Test2 BIT OUTPUT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tblToday WHERE Date = GetDate()) SET @.Test1 = 1 ELSE SET @.Test1 = 0
IF EXISTS(SELECT * FROM tblToday WHERE Date = DateAdd(day, 1, GetDate()))
SET @.Test2 = 1 ELSE SET @.Test2 = 0
------- This is the End ----
GO

This returns two output values. I normally put the output parameters at the end of the declaration.

Regards

Clive Chinery