How can I make table name a variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How can I make table name a variable?

Author

Topic

Petar_T
Starting Member

8 Posts

Posted - 2010-12-22 : 01:54:27

Hi, I am learning sql, I know very little about it.

I am interested if this what I wish is possible.

For example,
I would like to have something like a function, that, when excuted, similar like this...:

execute fn_displayTable Employees

would give me the result like I have typed:
select * from Employees

It would be like if I would have a variable instead of a table name, similar like:
select * from @str1

I came upon this, becaouse I wanted to have a table that could hold something like a foreign key but to the other table later yet to be builded, or better said like this:

If a have a table A

create table A(
A_key int primary key,
out_id int,
out_table_name nvarchar(50),
)

I would like to hold a refernece in a table A row to , for example, table B, but, at the moment of the creation of a table A, I don't know that there will be existing table B.

Thank You a lot.

ps, I appologize for the lack of knowledge...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-22 : 02:48:41

What i understand is that the variable will hold the table name during execution.

If my understanding is correct then one way to acheive this is using dyamic sql

Examle:

@str1 ='Employee'
Exec('select * from ' + @str1)

In case you wish to use dynamic sql then also have a look at:
http://www.sommarskog.se/dynamic_sql.html

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2010-12-22 : 05:27:28

Hi,

What i understand is that the table name hold as variable type, through that variable u would like to
retrieve the result...

As per my suggestion.. i done one small query..

PROCEDURE:-
---------

CREATE PROCEDURE empProcedure @d_table VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(300);
SET @SQL = 'SELECT * FROM'+@d_table
EXEC (@SQL);
END
GO
EXECUTE :-
-------

DECLARE @d_table varchar(50)

SET @d_table= 'Employee'

EXEC empProcedure @d_table
******************

suggestion are welcome...

let me know if ur get any doubt...

Regards,
Magesh.M

Kristen
Test

22859 Posts

Posted - 2010-12-22 : 05:48:19

Sounds like a bad design concept to me.

We use VIEWs for things that are "not quite decided yet", so we might have a table

MyTable

and a view

MyTable_VIEW

something like


CREATE VIEW MyTable_VIEW
AS
SELECT [V_ID] = T.ID,
       [B_SomeColumn] = B.SomColumn
FROM MyTable AS T
     JOIN MyTable_B AS B
         ON B.ID = T.ID

we use this for "Description Lookups" for Foreign Key tables, and also where column names change - so that we can preserve the old column names for backwards compatibility.

But a generic function that can take a Table Name as a a parameter will be much more inefficient than a SQL Query with the actual table name included.

Petar_T
Starting Member

8 Posts

Posted - 2010-12-22 : 08:48:01

Thank You everybody.

I will now think a little about this. You gave me a good information.

Thank You pk_bohra, Magesh M. , and Kirsten.
Thank You, You understood me right what was my question.
I will think also about the Views, but for now it seems to me that what I waneted was really a function that takes TableName as a parameter, or a dynamic sql, : ))
Really, thank You for all Your advices and suggestions, it made me very happy now...

: )))) bye...

Petar_T
Starting Member

8 Posts

Posted - 2010-12-22 : 09:15:17

Thank You Magesh. M

It works.

(I have one more question, I will ask it in a new topic )

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-22 : 09:32:35

But once go thru the document
http://www.sommarskog.se/dynamic_sql.html

because your code is open for sql injection.

How abt this:

DECLARE @d_table varchar(50)

SET @d_table= 'Employee; delete from Employee;'

EXEC empProcedure @d_table

Petar_T
Starting Member

8 Posts

Posted - 2010-12-22 : 09:55:28

Thank You, I will..

hm...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources