asked on

store procedure returning two values

Hi,

Normally when using stored procedure we only return the records or return a value, in this case we're returning both.
Kindly assist how can we return both, one for a return_value and the records to the dataset.

we wanted to display a message "no records found" else return the result. (I'm not sure if this is the right way to do it)

Thanks.

create procedure myproc
as
declare @nRowcount int

select a,b,c from table1 where a='something'

select @nRowCount = @@rowcount
return @@rowcount
Go

Private Function GenerateSource() As DataTable
Dim con As New SqlConnection(".....")
Dim cmd As SqlCommand = con.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SP_MYPROC"
cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))

Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds,"DataSetName")
Return ds.Tables("DataSetName")
cmd.dispose
cmd.parameters.clear
con.close

IF return_value = 0 then
Label1.text="No Records found."
End function

SOLUTION

djhex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EdwardPeter

ASKER

djhex,

Amazing !!!

My apology for asking, can you kindly assist which part this goes too so that we can display label1.text = "No Records found."

Thanks.

SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_MYPROC";
dr = cmd.ExecuteReader);
SqlDataReader dr= new SqlDataReader();
dr.Read();
int numberOfRecords=dr.RecordsAffected;

ASKER CERTIFIED SOLUTION

raterus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

djhex

qlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_MYPROC";
dr = cmd.ExecuteReader);
SqlDataReader dr= new SqlDataReader();
dr.Read();
int numberOfRecords=dr.RecordsAffected;
if( numberOfRecords==0 ){
lblmessaje.text ="No records found";

}else
{
// Make whatever you need

}

b1xml2

1. This is OO so the return type is of one type the DataTable, if you want to return more than 1 type, that is considered really really bad form (more so when the types have got nothing in common). Go to scripting if you want this kind of uncertainty.

2.
Private Function GenerateSource() As DataTable
Dim con As New SqlConnection(".....")
Dim cmd As SqlCommand = con.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SP_MYPROC"
cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(dt)
Return dt
End Function

There is nothing wrong with getting the datatable and then deciding whether to change the label text or not. That's called maintainable code. Write code that reflects the process.

EdwardPeter

ASKER

b1xml2,

Inorder to acomplish this, I need to learning scripting in asp.net ? (write code that reflects the process? ).

I really want to learn more, hope you could kindly guide me to the right track.

Thanks.