Use variable in select statement

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)

Use variable in select statement

Author

Topic

SpongeBert
Starting Member

2 Posts

Posted - 2007-06-28 : 06:42:14

Hi Y'all,

I have simple stored procedure with following simple select statement:
Select * from users

Now i want to use this stored procedure in one database and want to collect data from more than one database. Make it like:
Select * from db1.dbo.users

So i want to make the word after "from" variable. How can this be done?
Thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-28 : 06:45:08

[code]Declare @text varchar(8000),
@dbname varchar(255)

set @dbname ='somedb'

set @text = 'select * from ' + @dbname + '.dbo.users'
exec(@text)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-28 : 07:04:27

Whats wrong with not using variable?

Madhivanan

Failing to plan is Planning to fail

SpongeBert
Starting Member

2 Posts

Posted - 2007-06-28 : 08:34:40

Allrighty, This Works. Thanks
Now i want to know how this should be done in a function like this:

CREATE FUNCTION [dbo].[fn_Tabelbestaan]
(
@databasenaam nvarchar(100)
)
RETURNS table
AS
RETURN
(
DECLARE @sSql nvarchar(1000)
SET @sSql = 'select * from '+@databasenaam+'.dbo.sysobjects where name like ''USERS'' '
EXEC sp_sqlexec @sSql

)

Hope you can help me out,
Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-28 : 08:57:56

You can't!
Use Stored procedure instead.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-28 : 09:02:55

quote:
Originally posted by SpongeBert

Allrighty, This Works. Thanks
Now i want to know how this should be done in a function like this:

CREATE FUNCTION [dbo].[fn_Tabelbestaan]
(
@databasenaam nvarchar(100)
)
RETURNS table
AS
RETURN
(
DECLARE @sSql nvarchar(1000)
SET @sSql = 'select * from '+@databasenaam+'.dbo.sysobjects where name like ''USERS'' '
EXEC sp_sqlexec @sSql

)

Hope you can help me out,
Thanks!

You cant use Dynamic sql inside a function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources