Accessing SQL Express Stored procedures via VB.NET - Unexpected output

Question

Post reply

Accessing SQL Express Stored procedures via VB.NET - Unexpected output

johnfermor

SSC Enthusiast

Points: 151
More actions
October 27, 2007 at 8:02 am

#178805

Hi

I have some experience in deveoping VB.NET apps with static SQL but have been reading about the benefits of stored procedures and would like to start implementing them.

Having googled and found nothing that will teach me the basics, I found this; http://qa.sqlservercentral.com/articles/Stored+Procedures/usingparameterswithstoredprocedures/2004/

Although I have tried this example and I can connect to the database and the procedure runs, the msgbox statements are empty as no value appears to be returned from SQL. I can't figure out why.

I'm sure this something really stupid that I've done (or not - as the case may be) but I've wasted about 2 days trying to find documentation suitable for a newbie like me.

Any help is greatly appreciated.

John

Here's the code;

' ======================

SQL Express Stored Procedure

' ======================

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_AddTwoIntegers]

-- Add the parameters for the stored procedure here

@FirstNumber int = 5,

@SecondNumber int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Declare @sum int

Set @sum = @FirstNumber + @SecondNumber

Return @sum

END

' ==============

VB.NET Calling Code

' ==============

and here is the VB.NET calling code.

'You may need to modify the connection string

Dim conn As New SqlConnection("Integrated Security=SSPI;" & "Persist Security Info=False;Initial Catalog=northwind;" & "Data Source=SERVER1\SQLEXPRESS;Packet Size=4096;")

conn.Open()

'set up the command object

Dim myCommand As New SqlCommand("usp_AddTwoIntegers", conn)

myCommand.CommandType = CommandType.StoredProcedure

'add the first two parameters

myCommand.Parameters.AddWithValue("@FirstNumber", 5) 'this one is optional

myCommand.Parameters.AddWithValue("@SecondNumber", 5)

'The output and return parameters must be created as objects

Dim myAnswer As New SqlParameter("@Answer", SqlDbType.VarChar, 30)

myAnswer.Direction = ParameterDirection.Output

Dim mySum As New SqlParameter()

mySum.Direction = ParameterDirection.ReturnValue

MsgBox(myAnswer.Value)

MsgBox(mySum.Value)

==================

SQL ServerExpress 2005

VB.NET 2005

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Stored Procedures return values by means of parameters and not by return statement...The return statement in a procedure marks the end of execution and also the execution status of the procedure (i.e. success or failed).

But the return statement in an function is used to return a value to the calling method....

Addition Program through Stored Procedures



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_AddTwoIntegers]

-- Add the parameters for the stored procedure here

@FirstNumber int = 5,

@SecondNumber int = 0,

@Answer int OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Set @Answer = @FirstNumber + @SecondNumber

END

Eg.

DECLARE @Answer INT

EXECUTE [dbo].[usp_AddTwoIntegers] 5, 7, @Answer OUTPUT

SELECT @Answer

Addition Program through Function



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[fnAddTwoIntegers]

(

@FirstNumber int = 5,

@SecondNumber int = 0

)

RETURNS INT

AS

BEGIN

RETURN( @FirstNumber + @SecondNumber )

END

Eg.

SELECT dbo.fnAddTwoIntegers( 19, 9 ) AS [SumOFTwoIntegers]

--Ramesh

johnfermor SSC Enthusiast Points: 151 More actions · Answer 2

johnfermor

SSC Enthusiast

Points: 151

October 28, 2007 at 2:26 am

#746044

Okay Ramesh

I knew it had to be something I was missing!

Thanks for your quick response.

John

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 3

I think your issue isn't going to be solved by changing your proc into a UDF... looking at your calling code you don't appear to be actually executing your SQL. eg you will need a SqlCommand.ExecuteNonQuery(), SqlCommand.ExecuteScalar() or SqlCommand.ExecuteReader() or similar before trying to retrieve anything returned from the SQL code.

Stored procs return data to the caller one of 3 ways:

1) via a result set which you consume in your dotNET ADO code as a recordset (eg SELECT your data in the proc, and connect a SqlDataReader to it via ExecuteReader())

2) via OUTPUT parameters. Declare your proc parameters with the OUTPUT keyword, add them to your ADO parameters collection with Direction = Output or Direction = InputOutput, execute your SqlCommand then read back the parameter values.

3) via the return code. Same as option 2 above, except that the parameter direction is ReturnValue and must be the first parameter declared in your parameter collection. Only supports int data.

For the simple Add proc example you gave it would be most efficient to use a single OUTPUT parameter with ExecuteNonQuery() to retrieve the result (saves the overhead of recordset construction under the covers with ExecuteScalar()).

Regards,

Jacob