Multiple select statements in a stored procedure s

问题:

Is it possible to add multiple select statements in a single stored procedure . The select statements are getting data from same tables. If yes, could anybody provide an example in adding multiple select statements, which retrieve data from different tables in a stored procedure.

Actually I am having list like state,city, university,college,department in my maintenance (same) table. As per the query i want execute the query and populate the value in my drop down list .

回答1:

This proc will return mutiple result sets to the client

CREATE PROC whatever
AS
SELECT col1, col2 FROM Table1
SELECT col3, col4, col5 FROM Table2
SELECT col1, col3 FROM Table3
GO

You can use DataAdaptor.Fill and then you can DataTable(0), DataTable(1) and DataTable(2)

Or iterate over them with DataReader.NextResult

If you have "all data in one table" then you have a bad design: sql performance of a lookup table

回答2:

Not sure what you are trying to do exactly, but for example this would work:

  select id,name from table1 where code<=500
  union all
  select id,name from table2 where code >=1000 and code <=2000