One SP to retrieve data from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

One SP to retrieve data from multiple tables

Author

Topic

kallileo
Starting Member

21 Posts

Posted - 2007-04-26 : 05:44:11

I have 7 tables that have onle one record and I read only one cell from my ASP.NET page. The tables are identical.

What I want is to create a SP to read the values at once from 7 the seven tables.
At the moment I use one SP for every table.

ALTER PROCEDURE dbo.switch1
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
SELECT [rawvalue] FROM [table_1]
/* SET NOCOUNT ON */
RETURN

nduggan23
Starting Member

42 Posts

Posted - 2007-04-26 : 07:26:40

What do you need to return? a list? or a single value?
if its a list

select [columnname] from table1
union
select [columnname] from table2
etc...

where the columnnames are the same.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 07:27:25

If table structure is identical, you can try using UNION ALL to retrieve values from all the 7 tables.

Select Col1, Col2, ... from Table1
union all
Select Col1, Col2, ... from Table2
union all
...
Select Col1, Col2, ... from Table7

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kallileo
Starting Member

21 Posts

Posted - 2007-04-26 : 07:50:00

I read single values with a datareader and I set a variable(result) equal to the boolean value I read from the db.

SQLconn.Open()
Dim SelectCmd1 As New SqlCommand("switch", SQLconn)
SelectCmd1.CommandType = CommandType.StoredProcedure
Dim result1 As Boolean
Dim myDataReader1 As SqlDataReader
myDataReader1 = SelectCmd1.ExecuteReader()
myDataReader1.Read()
result1 = myDataReader1.GetBoolean(0)
SQLconn.Close()

The problem is that doing it this way I have to open/close the db connection 7 times.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-26 : 07:55:30

union/union all is to combine different table and get as result set. your requirement is confusing...

Is it like ..

1.What the stored procedure is returning? tbale/single value
2. Whats ur equirement to get the resultset from the procedure or what?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 07:58:12

Please post the DDL for the 7 tables with sample data for each table and the SINGLE VALUES result that you want/

kallileo
Starting Member

21 Posts

Posted - 2007-04-26 : 08:42:25

The tables structure:

device rawvalue reading timestamp
switch1 (Boolean) correct xx-xx-xxxx

I nead only the [rawvalue]s from the seven tables to initialize 7 controls on my ASP.NET page on Page_Load.

Until now I used this code to intiliaze the controls:

SQLconn.Open()
Dim SelectCmd1 As New SqlCommand("switch1", SQLconn) <<switch1=SELECT rawvalue from table1>>
SelectCmd1.CommandType = CommandType.StoredProcedure
Dim result1 As Boolean
Dim myDataReader1 As SqlDataReader
myDataReader1 = SelectCmd1.ExecuteReader()
myDataReader1.Read()
result1 = myDataReader1.GetBoolean(0)
SQLconn.Close()
initialize1(result1)

SQLconn.Open()
Dim SelectCmd2 As New SqlCommand("switch2", SQLconn) <<switch2=SELECT rawvalue from table2>>
SelectCmd2.CommandType = CommandType.StoredProcedure
Dim result2 As Boolean
Dim myDataReader2 As SqlDataReader
myDataReader2 = SelectCmd1.ExecuteReader()
myDataReader2.Read()
result2 = myDataReader2.GetBoolean(0)
SQLconn.Close()
initialize2(result2)
.
.
.

I just want to pass the result(1,2,3,4...) to initialize(1,2,3...) using only one SP and without opening/closing the connection
7 times.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources