SQL Server Forums
Profile | Register | 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?
 New Topic  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
22403 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000