Multiple Selects in a Stored Proc

I need to perform 2 selects in a stored procedure, but only return data from the second..

eg.

first select gets the proper table to search from a transaction summary table

select category from trans summary where transsummary.transid = @.transid

based on the category returned something like this

if category = 1
then tabletosearch = table1
end if
if category = 2
then tabletoseach = table 2
endif

select * from @.tabletosearch


Is this possible, or am I going about this the wrong way?...
As you probably can tell I am new at this.
Thanks folksTry something like...

select @.category = (select category from trans summary where transsummary.transid = @.transid)

if @.category = 1
then @.tabletosearch = table1
end if
if @.category = 2
then @.tabletoseach = table 2
endif

select * from @.tabletosearch|||DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||That worked great if I used SELECT * but as soon as I try to use only specific fields.....

Originally posted by achorozy

DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||I'm sorry if the example I showed was inline with your question, after all your question did have 'SELECT *'.

Since you don't tell us why it doesn't work when you list out the columns my only guess is that each table has different columns.

So why not just do this:

DECLARE @.category int

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SELECT col1, col2, col3 FROM table1

if (@.category = 2)
SELECT colA, colB, colC FROM table2
Sorry if I sound a bit off, but I've never asked a question and all of my 200+ posting has come from answering question, trying too anyways. So I get tired after awhile when someone says "It does work".
What doesn't work? What error did you get? Why doesn't it work?|||My apologies, but if it isnt obvious already, I am new to SQL and these newsgroups..again my apologies..

So far I am getting to about 7/8ths of the way thru want I need to do.
SO I will do, what I should have done the first time. give all the details

Here is what I want, I am able to get it to the first portion to work (select if...) so I won't go into more detail on that.

I need to be able to get data from 3 tables and only certain fields from each table

so

select IDCode.t1, IdDetail.t1, SalesRep.t1, SalesRep1Addr.t2, SalesRepEmail.t2, OfficeId.t2, OfficeAddr.t3, OfficeEmail.t3

S.. t1 contains a unique id from t2, and t2 contains a unique id from t3...

and obviously the the corresonding Sales Rep Detail & Office Detail...

Thanks so much for the help