October 27, 2007 at 8:02 am
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://www.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
October 27, 2007 at 9:55 am
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
October 28, 2007 at 2:26 am
Okay Ramesh
I knew it had to be something I was missing!
Thanks for your quick response.
John
October 29, 2007 at 12:21 am
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
October 29, 2007 at 1:12 am
johnfermor ,
I thought this will fairly give you some idea on SP stuff and calling the same into sql's. Please go through & try this....
http://www.macronimous.com/resources/stored_procedures_for_ASP_and_VB_Programmers.asp
November 3, 2007 at 7:38 am
Okay thank you to everyone who responded.
The link above looks great - I guess I'll just go through the article and hit the books again!
Many thanks..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply