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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-22 : 23:55:52
|
Jamie writes "Dear Sean,
The kudosTM is all yours for an answer to this one! As part of the customisation features for our site, I create a global temp table for each validated user, based on their login id. Using pubs, it'd be something along these lines
create procedure kudos_to_you
@person_id int
as
declare @tbl_nm varchar(10)
declare @exec_str varchar(512)
set @tbl_nm = '##tmp_' + cast(@person_id as varchar(6))
set @exec_str = 'select au_id,au_fname,au_lname into ##tmp_'
exec(@exec_str)
--do some updating and other stuff...
/* hit problem here*/
return
go
I want to be able to return an rst directly from this same stored procedure, but: - Exec cmd won't do the job (ie. exec ('select * from ' + @tbl_nm)).
- Within stored procedures, variables can't be used to represent database objects (tables, columns etc), and have to be referred to directly by name.
As a consequence, I have to execute the sp through ado, return to the asp page, construct an sql string and execute a second query to retreive data.
For ease of maintenance and consistency, I want to move all db-related code into stored procedures, and this one is the last problem I've to get around
Any advice would be heaps appreciated!
tia,moi" |
|
|
|
|
|
|
|
HowTo: Return an rst from ##tmp table through sp