Stored procedure return value

[Guest]

Hello -

I need a good example of how to take a return value from a stored procedure
and use it in vb code. I have an app that searches a database by city and
state. If a user makes a typo, the info sent to the stored procedure makes
the stored procedure fail. I want to be able to display a label indicating
they should try again, if this is the case. I can't use row count, because
the stored procedure doesn't get that far.

What exactly do I put in my stored procedure and what do I put in my vb?

Any help will be greatly appreciated!

 

[Robin Tucker]

What kind of typo would cause the SP to fail in this way? and how does the
SP fail? (just curious)

 

[Zoury]

Hi Sandy ! :O)

Assuming you're using SQL Server you can declare an output parameter like
this (not tested.. and my t-sql is getting rusty) :
--
create procedure my_proc(
@id int,
@return_value varchar(200) output,
)
as
begin

select f1, f2, fn from mytable where id = @id
set @return_value = 'ok'

end
--

you would call the procedure like this (not tested either) :
'***
Dim cmd As New SqlCommand
cmd.CommandText = "sp_test"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = New SqlConnection("my_connection_string")

cmd.Parameters.Add("@id", SqlDbType.Int,
ParameterDirection.Input).Value = 1
cmd.Parameters.Add("@return_value", SqlDbType.Varchar, 200, Nothing,
ParameterDirection.Output)

Dim ds As New DataSet
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)

Console.WriteLine(cmd.Parameters("@return_value").ToString())
'***

 

[Guest]

Hi Robin:

Actually anything put into City that is not in my database will cause the SP
not to work. The error which I get in my Catch ex is Source=System.Data,
Message="Cannot find Table 0."

When I debug, the actual error is an IndexOutOfRangeException. I could
catch this error in a Catch ExORE IndexOutOfRangeException, but I think it's
better to handle it at the Sql Server level. Don't know if this is correct
thinking or not.

 

[Guest]

Hi Zoury -

Thanks for your response. I tried your sproc and couldn't get it to work.

The return value I really want is the one automatically returned by Sql
Server. It is my understanding that Sql Server always returns a value; if
the sproc executes successfully, it is zero. Otherwise, I believe it returns
a -1.

For simplicity, say we have the following:

Create procedure spCityState
(
@City char(50),
@State char(2)
)
AS
Select @City, @State From Zips Where City = @City and State = @State

When the procedure hits the @City when a typo is put in, the procedure can
obviously no longer finish. I would like to put language in the SP so it
stops working at that point and returns the value to me. (I'm thinking along
the lines of an If Not Exists(Select ......) RETURN before the actual select
statement.

I then would like to use the value from RETURN in my VB code, i.e. if the
value returned is < something then something is displayed in a label to tell
the user there was a typo made and to try again.

I need to know how to designate that RETURN value in the stored procedure
and then access it from my VB code.

Again, thanks for your response. Any suggestions?

 

[Zoury]

The return value I really want is the one automatically returned by Sql

Server. It is my understanding that Sql Server always returns a value; if
the sproc executes successfully, it is zero. Otherwise, I believe it returns
a -1.

ok. i've never used it (and i can't test it for now) but I think you need
something like this :
'**
cmd.Parameters.Add("@return_value", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue
'**

after the call, you should be able to read the value using something like
this :
'**
Console.WriteLine(cmd.Parameters("@return_value").Value)
'**

Note that if you use SqlCommand.ExecuteReader() to query your data, you
won't be able to read the value of any Output, InputOut and ReturnValue
parameters until you call the Close() (or Dispose()) method of the reader.