My First Stored Procedure

[Jim Heavey]

Hello, I am working on a school project and I decided that I should try to
create a stored procedure for this project.

I was very successful in creating a simple query which returned a bunch of
rows.

I changed the query to count the number of rows being returned in addition to
what I was returning in the query.

When I execute the procedure in QueryAnalizer, I get all the rows, but now I
get each row in it's own little grid.

When I change my ASP.Net program to call this new stored procedure, I only get
a single row when it is bound to the datagrid.

What do I need to get all the rows to show up in my grid?

Here is my vb code...

da.SelectCommand = New SqlCommand("proc_moviesOverdue2", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
param = da.SelectCommand.Parameters.Add(New SqlParameter("@totRecords",
SqlDbType.Int))
param.Direction = ParameterDirection.Output

da.Fill(ds, "LateMovies")
dv.Table = ds.Tables("LateMovies")
dv.Sort = lblSort.Text.Substring(8)
lblLateMovies.Text = "Overdue Movie List"
dgLateMovies.DataSource = dv
Page.DataBind()

 

[William \(Bill\) Vaughn]

Let's see the SP

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jim Heavey]

Here is the stored Procedure!!!!!!

create procedure proc_MoviesOverdue2
(
@totRecords int output
)
as
DECLARE overdueRentals cursor for
select m.title as 'Title', t.tape_number as 'Tape_Number', c.first_name,
c.last_name,
convert(varchar, r.date_due,101) as Due_date,
datediff(day, date_due, todays_date) as 'Days_Overdue'
from movies m, customer c, tapes t, rentals r, Video_store_values
where(m.movie_id = t.movie_id)
and t.tape_number = r.tape_number
and r.cust_number = c.cust_number
and return_date is null
and date_due < todays_date
order by Days_Overdue desc, Title asc, Tape_Number asc
BEGIN
set @totRecords=0
Open overdueRentals
if @@error <> 0
goto Failure
fetch next from overdueRentals
set @totRecords = @totRecords +1
while (@@FETCH_STATUS = 0) AND (@@ERROR=0)
BEGIN
if (@@error = 0)
set @totRecords = @totRecords +1
fetch next from overdueRentals
end


end

CLOSE overduerentals
deallocate overduerentals
return 0

FAILURE:
return -1



GO

 

[William \(Bill\) Vaughn]

Why not:

SELECT Count(*) FROM
movies m, customer c, tapes t, rentals r, Video_store_values
where(m.movie_id = t.movie_id)
and t.tape_number = r.tape_number
and r.cust_number = c.cust_number
and return_date is null
and date_due < todays_date

Your SP does not return a rowset but a single OUTPUT parameter. I don't see
where you have a SELECT to return even a single rowset.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jim Heavey]

The query you suggest only provides a single value, that being the row count.
It does not provide all the other fields that I need .... (select m.title as
'Title', t.tape_number as 'Tape_Number', c.first_name, c.last_name,
convert(varchar, r.date_due,101) as Due_date,
datediff(day, date_due, todays_date) as 'Days_Overdue'

When I run the procedure that I provided in the attachment, I get a single row
with all those fields. I am guessing that I must follow a different procedure
when I open up a cursor. When I did not open a cursor, but just ran the
query, no problem. When I opened up the cursor, while I get all the rows when
I run the procedure in Query Analyzer ( but again, I get each row in its own
grid), but I only get that single row when I jump down into ASP.Net.

I guess I need to get a book on how to work with stored procedures, as the
book I have only has a passing reference to how to do it.

I realize that I can get the "row count" from the property from the data table
when I fill the dataset, but my instructor says that this is not always
reliable and just for safety I should pull the count down from the stored
procedure if I am going to go to the trouble of writing my own stored
procedure. So that is why I am doing this...

 

[William \(Bill\) Vaughn]

Generally, server-side cursors are used to manage rowsets on the server
(when coded as you have done)--not to return rowsets to the client. An
ordinary SELECT should return a rowset of all qualifying rows. ADO.NET can
fetch all of these rows for you and return a count as well.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________