Returning A Dataset From A Procedure

Hi all





I have written a SQL Procedure to return all the results from a table and am writing a function to run the procedure (So that it is easier to use within my app). I have kinda got a bit confused. :oS





The SQL procedure Gallery_GetAllCetegoryPictures has one input variable (CategoryID) and returns a table procedure is something like SELECT * FROM Gallery WHERE CategoryID = @CategoryID.








The code im having trouble with is below:





Function GetAllCategoryPictures(ByVal CatID As Integer) As DataSet


Dim MyConnection As SqlConnection


Dim MyCommand As New SqlCommand


Dim MyParameter As SqlParameter


MyConnection = New SqlConnection(AppSettings("DSN"))


MyConnection.Open()





MyCommand.CommandText = "Gallery_GetAllCetegoryPictures"


MyCommand.CommandType = CommandType.StoredProcedure





MyParameter = MyCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4)


MyParameter.Direction = ParameterDirection.Input


MyParameter.Value = CatID





MyCommand.ExecuteNonQuery()








End Function








OK so that executes the procedure now I want to return the data within a dataset.





Do I need to use a data reader? if so how do I do it?





Thanks