dynamically FROM clause

Question

Post reply

dynamically FROM clause

neculai

Newbie

Points: 3
More actions
October 30, 2003 at 10:07 am

#176815

I need to get data from a diff. database and I need to use dynamically the FROM clause like: @database_name. dbo.@table_name .
Database name and table name being variables.
How can I do it.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

rs SSCrazy Points: 2819 More actions · Answer 1

TRY THIS

DECLARE @table VARCHAR(30)

DECLARE @owner VARCHAR(20)

DECLARE @Database VARCHAR(30)

DECLARE @sql VARCHAR(200)

SET @Database = 'YOUR DATABASE NAME'

SET @owner = 'dbo'

SET @table = 'YOUR TABLE NAME'

SET @sql = 'SELECT * FROM ' + @Database + '.' + @owner + '.' + @table

EXEC (@SQL)

neculai Newbie Points: 3 More actions · Answer 2

Thanks for help.

It is working if you do not have a WHERE clause.

If you try to use a WHERE clause using a variable will not work.

The same if you try EXECUTE sp_executesql to use parameters.

rs SSCrazy Points: 2819 More actions · Answer 3

IT WORKS FOR WHERE CLAUSE ALSO. TRY THE FOLLOWING EXAMPLE.

CREATE TABLE TESTTB

(id INT, Name CHAR(20))

INSERT INTO TESTTB VALUES(1,'SQL')

INSERT INTO TESTTB VALUES(2,'T-SQL')

DECLARE @table VARCHAR(30)

DECLARE @owner VARCHAR(20)

DECLARE @Database VARCHAR(30)

DECLARE @sql VARCHAR(200)

DECLARE @where VARCHAR(30)

SET @Database = 'Tempdb'

SET @owner = 'dbo'

SET @table = 'TESTTB'

SET @where = ' WHERE name = ''SQL'''

SET @sql = 'SELECT * FROM ' + @Database + '.' + @owner + '.' + @table + @where

EXEC (@SQL)

DROP TABLE TESTTB

neculai Newbie Points: 3 More actions · Answer 4

Thanks. It worked.

I had to use CAST for the Where clause.

DECLARE @ID int

SET @ID = 1

SET @where = ' WHERE ID = '+ cast(@ID as varchar(8))

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I am reiterating myself, but be aware that this flexibility comes at a certain cost. Read this http://www.algonet.se/~sommar/dynamic_sql.html and decide then if dynamic SQL is really what you want.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Oh, I forgot!

Dynamic SQL can be used with almost every T-SQL command.

This is something I use for administrational purposes



CREATE PROCEDURE sp_createbinarytable @tname nvarchar(50)  AS



DECLARE @stmt nvarchar(400)



SET @stmt = N'CREATE TABLE ' + @tname + 

' ( [id] [int] IDENTITY (1, 1) NOT NULL, 

 [image] NULL ,

[filename] [varchar] (50) NULL ,

[description] [varchar] (100) NULL ,

[sender] [varchar] (50) NULL) 

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXEC sp_executeSQL @stmt



SET  @stmt = N'CREATE  UNIQUE  CLUSTERED  INDEX [SK_ID] ON [dbo].['+@tname+']([id]) ON [PRIMARY]'

EXEC sp_executeSQL @stmt



SET @stmt = N'ALTER TABLE [dbo].['+@tname+'] WITH NOCHECK ADD 

CONSTRAINT [PK_'+@tname+'_1] PRIMARY KEY  NONCLUSTERED 

(

[id]

)  ON [PRIMARY] '

EXEC sp_executeSQL @stmt



SET @stmt = N'GRANT SELECT ON [dbo].['+@tname + '] TO [REW_User]'

EXEC sp_executeSQL @stmt

SET @stmt = N'GRANT SELECT ON [dbo].['+@tname + '] TO [FAI_User]'

EXEC sp_executeSQL @stmt

SET @stmt = N'GRANT SELECT ON [dbo].['+@tname + '] TO [FCO_User]'

EXEC sp_executeSQL @stmt

SET @stmt = N'GRANT SELECT ON [dbo].['+@tname + '] TO [VORSTAND]'

EXEC sp_executeSQL @stmt

SET @stmt = N'GRANT REFERENCES, SELECT, INSERT, DELETE, UPDATE ON [dbo].['+@tname+'] TO [a5xo3z1]'

EXEC sp_executeSQL @stmt

GO

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Sorry, just saw something that shouldn't be done.

What have we learned from other threads?

Never prefix your sprocs with sp_...

Must be some kind of legacy procedure. I bet someone else here wrote it

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 8

quote:
Thanks. It worked.
I had to use CAST for the Where clause.
DECLARE @ID int
SET @ID = 1
SET @where = ' WHERE ID = '+ cast(@ID as varchar(8))

Why not just declare your variable as an varchar so you don't have to do a cast?

mdavydov SSC Rookie Points: 39 More actions · Answer 9

Just additional information: Stored procedures with sp_ prefix will be searched first in the master database, because this prefix is used for system stored procs.

That's way it is a good idea to avoid using sp_ prefix for custom stored procedures.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Oops, thanks!

I should included some lately threads http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17674

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17637

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mdavydov SSC Rookie Points: 39 More actions · Answer 11

Thanks Frank! Links are very good and it is good to know more about a subject.

P.S. Happy Halloween to everyone!