select * from table user - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

select * from table user

Author

Topic

tedew
Starting Member

22 Posts

Posted - 2005-12-28 : 03:04:50

hello:)
Is this possible that I will be get data from user's tabel??
I wrote stored_procedure , the owner of this procedure is DBO, and this procedure don't see user's procedures:( ,I'm traing this:

....

delcare @us varchar(50)
select @us=current_user

select * from @us.tab
....

but it doesn't work :(

when I replece @us to 'peter' - it's working

???
bye,
sorry for my english

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-28 : 03:23:37

Do you want to pass the table name dynamically ?
if yes then this is how u should do exec('select * from '+@us)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-28 : 03:33:39

You can also use sp_executeSQL. The advantage of using sp_executesql over exec is

1) By using sp_executesql, query plans are more likely to be reused.

2) sp_executesql has a more complex signature and parameters can be passed into the sql statement.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 03:47:05

More on Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

tedew
Starting Member

22 Posts

Posted - 2005-12-28 : 04:40:28

thanks,
bu i have a litle problem
I have done this:

declare @SQL nvarchar(4000)
declare @user sysname

set @user=current_user

set @SQL= 'select * from [' + @user +'].tab'

exec (@SQL)

it works fine , but I don't know how to use it in Cursor :(
I tray this :

declare @SQL nvarchar(4000)
declare @user sysname

set @user=current_user

set @SQL= 'select * from [' + @user +'].tab'

Declare cursor C1 for exec (@SQL)

but this dosen,t work :(

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-28 : 05:16:57

for that u need to put ur declare statement in the string as well. but why do you want to do this?

set @SQL= 'Declare cursor C1 for select * from [' + @user +'].tab'

exec (@SQL)
open C1....

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-28 : 05:39:53

Or else you can use temporary table for this

create table #d(t datetime)

declare @SQL nvarchar(4000)
declare @user sysname

set @user=current_user

set @SQL= 'select * from [' + @user +'].tab'

insert into #d
exec(@SQL)

and then use #d in your cursor

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources