Stored Procedure with multiple select statements

[vertigo262]

Is it possible to use to select statements in a stored procedure?

I am building a movie rating system, what I am doing is creating a table with movies and individual user ratings.

The code needs to get a count of the ratings, then the sum of them to get the percentage.


I am getting a error
Exception Details: System.IndexOut OfRangeExceptio n: columncount

it seems my stored procedure returns the first select, but the value for the second select is non existent.

Is this possible? I've never tried a Stored Procedure with multiple selects?
and if so, what am I doing wrong?




Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT columncount = COUNT(*) FROM MovieRatings WHERE(MoviesID = @MoviesID)





---------------------------------------------------------------------------------------------------




Vb.net code ---------------------------------------------------------------------------------------


Public Function GetMovieRating( ByVal MoviesID As Integer) As Integer

Dim Conn As New SqlConnection(C onnectionString )
Dim CmdSelect As New SqlCommand("Get MovieRating", Conn)
CmdSelect.Comma ndType = CommandType.Sto redProcedure
CmdSelect.Param eters.Add(New SqlParameter("@ MoviesID", MoviesID))
' Try
Conn.Open()
Dim Dr As SqlDataReader = CmdSelect.Execu teReader()

While Dr.Read()

Dim Rating As Integer = Dr("RatingSum" ) / Dr("columncount ")

Return Rating

End While

' Catch ex As Exception
' Throw New ApplicationExce ption("Data Error")
' Finally
Conn.Close()
' End Try

End Function


---------------------------------------------------------------------------------------------------

[prabunewindia]

hi friend,
try to use SqlDataAdapter instead of datareader
and get the output into DataSet
now ur dataset will have two tables
1.DatasetName.T ables[0]
2.DatasetName.T ables[1]

code

Dim Conn As New SqlConnection(C onnectionString )
Dim CmdSelect As New SqlCommand("Get MovieRating", Conn)
CmdSelect.Comma ndType = CommandType.Sto redProcedure
CmdSelect.Param eters.Add(New SqlParameter("@ MoviesID", MoviesID))
SqlDataAdapter da=new SqlDataAdapter( (CmdSelect);
Dataset ds=new Dataset();
da.Fill(ds);

use this dataset for ur process
Prabu

Is it possible to use to select statements in a stored procedure?

I am building a movie rating system, what I am doing is creating a table with movies and individual user ratings.

The code needs to get a count of the ratings, then the sum of them to get the percentage.


I am getting a error
Exception Details: System.IndexOut OfRangeExceptio n: columncount

it seems my stored procedure returns the first select, but the value for the second select is non existent.

Is this possible? I've never tried a Stored Procedure with multiple selects?
and if so, what am I doing wrong?




Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT columncount = COUNT(*) FROM MovieRatings WHERE(MoviesID = @MoviesID)





---------------------------------------------------------------------------------------------------




Vb.net code ---------------------------------------------------------------------------------------


Public Function GetMovieRating( ByVal MoviesID As Integer) As Integer

Dim Conn As New SqlConnection(C onnectionString )
Dim CmdSelect As New SqlCommand("Get MovieRating", Conn)
CmdSelect.Comma ndType = CommandType.Sto redProcedure
CmdSelect.Param eters.Add(New SqlParameter("@ MoviesID", MoviesID))
' Try
Conn.Open()
Dim Dr As SqlDataReader = CmdSelect.Execu teReader()

While Dr.Read()

Dim Rating As Integer = Dr("RatingSum" ) / Dr("columncount ")

Return Rating

End While

' Catch ex As Exception
' Throw New ApplicationExce ption("Data Error")
' Finally
Conn.Close()
' End Try

End Function


---------------------------------------------------------------------------------------------------

[gagandeepgupta16]

Is it possible to use to select statements in a stored procedure?

I am building a movie rating system, what I am doing is creating a table with movies and individual user ratings.

The code needs to get a count of the ratings, then the sum of them to get the percentage.


I am getting a error
Exception Details: System.IndexOut OfRangeExceptio n: columncount

it seems my stored procedure returns the first select, but the value for the second select is non existent.

Is this possible? I've never tried a Stored Procedure with multiple selects?
and if so, what am I doing wrong?




Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT columncount = COUNT(*) FROM MovieRatings WHERE(MoviesID = @MoviesID)





---------------------------------------------------------------------------------------------------




Vb.net code ---------------------------------------------------------------------------------------


Public Function GetMovieRating( ByVal MoviesID As Integer) As Integer

Dim Conn As New SqlConnection(C onnectionString )
Dim CmdSelect As New SqlCommand("Get MovieRating", Conn)
CmdSelect.Comma ndType = CommandType.Sto redProcedure
CmdSelect.Param eters.Add(New SqlParameter("@ MoviesID", MoviesID))
' Try
Conn.Open()
Dim Dr As SqlDataReader = CmdSelect.Execu teReader()

While Dr.Read()

Dim Rating As Integer = Dr("RatingSum" ) / Dr("columncount ")

Return Rating

End While

' Catch ex As Exception
' Throw New ApplicationExce ption("Data Error")
' Finally
Conn.Close()
' End Try

End Function


---------------------------------------------------------------------------------------------------

hi
there can be multiple select query, the thing is how you return the output.
having two output parameters or returning to datareader.

Your Stored Procedure -----------------------------------------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[GetMovieRating] ( @MoviesID int )
AS

SELECT Sum(Rating) As RatingSum FROM MovieRatings WHERE (MoviesID = @MoviesID)

SELECT COUNT(*) as columncount FROM MovieRatings WHERE(MoviesID = @MoviesID)


hope this solves your prob

[vertigo262]

I guess my question is, how do I retreive both those column values.

the reason I am trying to use a datareader is so I can grab both values, and dived them. then put them into a variable and return the function.

I don't know if you can grab values out of a dataset like that. I've never used a dataset in that way. I've just used them to fill controls with a dataset.

any help would be appreciated.

[vertigo262]

Ok, it's working.
I took prabunewindia's advice.

I created a dataset and pulled the 2 variables.

here is my code ----------------------------------------------------------------

Dim Conn As New SqlConnection(C onnectionString )
Dim CmdSelect As New SqlCommand("Get MovieRating", Conn)
CmdSelect.Comma ndType = CommandType.Sto redProcedure
CmdSelect.Param eters.AddWithVa lue("@MoviesID" , MoviesID)

Dim Da As New SqlDataAdapter( CmdSelect)
Dim Ds As New DataSet

'Fill the Dataset
'Try
Da.Fill(Ds)

Dim RatingSum As Integer = Ds.Tables(0).Ro ws(0)("RatingSu m").ToString ()
Dim columncount As Integer = Ds.Tables(1).Ro ws(0)("columnco unt").ToString( )



Dim Rating As Integer = RatingSum / columncount

Return Rating



'Catch ex As Exception
Throw New ApplicationExce ption("Data Error")
'Finally
Conn.Close()
'End Try

-----------------------------------------------------------------


My only concern is that this code is going to query the database for every movie listed. I hope this method of counting and suming of a database table for every movie in a recordset isn't too taxing on the database as it gets large and there are a lot of users using the system.

Thanks for all your help