Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How can I make table name a variable?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petar_T
Starting Member

8 Posts

Posted - 12/22/2010 :  01:54:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 12/22/2010 :  02:48:41  Show Profile  Reply with Quote
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
Go to Top of Page

MageshkumarM
Yak Posting Veteran

India
61 Posts

Posted - 12/22/2010 :  05:27:28  Show Profile  Reply with Quote
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




Edited by - MageshkumarM on 12/22/2010 05:30:01
Go to Top of Page

Kristen
Test

United Kingdom
22858 Posts

Posted - 12/22/2010 :  05:48:19  Show Profile  Reply with Quote
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.
Go to Top of Page

Petar_T
Starting Member

8 Posts

Posted - 12/22/2010 :  08:48:01  Show Profile  Reply with Quote
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...
Go to Top of Page

Petar_T
Starting Member

8 Posts

Posted - 12/22/2010 :  09:15:17  Show Profile  Reply with Quote
Thank You Magesh. M

It works.


(I have one more question, I will ask it in a new topic )
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 12/22/2010 :  09:32:35  Show Profile  Reply with Quote
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
Go to Top of Page

Petar_T
Starting Member

8 Posts

Posted - 12/22/2010 :  09:55:28  Show Profile  Reply with Quote
Thank You, I will..

hm...
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000