Get a Table NAme in a Variable

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)

Get a Table NAme in a Variable

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2010-11-09 : 02:49:28

DECLARE @TableName VARCHAR(50)
SET @TableName=(SELECT TableName FROM CRMCustomer WHERE CRMId=21)

Here am getting a Table name in a variable.. Table name is coming..

SELECT * FROM @TableName

But when I using the variable name to select the table its not working fine. Asking to Declare the Table variable.

Please help me in this to Select the Table Details from the Selected Variable name.

Regards,
Kalaiselvan R
Love Yourself First....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 03:12:35

This is only possible with dynamic sql.
See here: http://www.sommarskog.se/dynamic_sql.html

No, you're never too old to Yak'n'Roll if you're too young to die.

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-09 : 04:40:22

Try this..

Declare @SQL varchar(1000)
DECLARE @TableName VARCHAR(50)
SET @TableName=(SELECT TableName FROM CRMCustomer WHERE CRMId=21)
set @SQL ='select * from '+@TableName
Execute(@SQL)

@Sathiesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 05:34:51

This is subject to sql injection and you need to take care of it

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 02:23:47

quote:
Originally posted by madhivanan

This is subject to sql injection and you need to take care of it

Madhivanan

Failing to plan is Planning to fail

I guess the way to do it is this



declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='tablename'
SET @SQL='select * from ' +  @TableName
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-10 : 08:21:46

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by madhivanan

This is subject to sql injection and you need to take care of it

Madhivanan

Failing to plan is Planning to fail

I guess the way to do it is this

declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='tablename'
SET @SQL='select * from ' + @TableName
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

PBUH

create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete from #t'
SET @SQL='select * from ' + @TableName
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

select * from #t

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 08:26:48

create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete from #t'
SET @SQL='select * from ' + quotename(@TableName)
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

...gives error and nothing is deleted

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 08:31:40

This one does not



create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects'
SET @SQL='select * from ' + quotename(@TableName) +';delete #t'
print(@sql)
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

drop table #t

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 08:34:52

quote:

Originally posted by Sachin.Nand

This one does not



create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects'
SET @SQL='select * from ' + quotename(@TableName) +';delete #t'
print(@sql)
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

drop table #t

PBUH

The point is, that <';delete #t'> isn't coming from outside.

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 08:39:48

quote:
The point is, that <';delete #t'> isn't coming from outside.

Oh sorry.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 08:57:25

quote:
The point is, that <';delete #t'> isn't coming from outside.

But then there is no need for using sp_executesql.Even this can suffice if I am not missing something.


create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete #t'
SET @SQL='select * from ' + quotename(@TableName)
exec(@sql)

drop table #t

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-10 : 09:55:11

quote:
Originally posted by webfred

create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete from #t'
SET @SQL='select * from ' + quotename(@TableName)
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

...gives error and nothing is deleted

No, you're never too old to Yak'n'Roll if you're too young to die.

Beware of limitation of quotename() function
http://beyondrelational.com/blogs/madhivanan/archive/2010/10/11/usage-of-quotename-function.aspx

Another method is to use derived table
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx

Madhivanan

Failing to plan is Planning to fail

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-11-10 : 19:27:08

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete from #t'
SET @SQL='select * from ' + quotename(@TableName)
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

...gives error and nothing is deleted

No, you're never too old to Yak'n'Roll if you're too young to die.

Beware of limitation of quotename() function
http://beyondrelational.com/blogs/madhivanan/archive/2010/10/11/usage-of-quotename-function.aspx

Another method is to use derived table
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx

Madhivanan

I tried all the above examples for curiosity sake, but all of them gives errors. Is there a better way I would appreciate as it keeps my pea brain ticking

Thanks to all

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 04:34:29

Post the code you used

Madhivanan

Failing to plan is Planning to fail

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-11-11 : 17:07:14

quote:
Originally posted by madhivanan

Post the code you used

Madhivanan

Failing to plan is Planning to fail

It is the same code you designed.
create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete from #t'
SET @SQL='select * from ' + @TableName
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

select * from #t

Thanks once again
Glen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-12 : 05:13:41

What is the error?

Madhivanan

Failing to plan is Planning to fail

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-11-12 : 21:55:45

I tried the same code but I am getting the following errors below
Olease show me where I am going wrong.

create table #t (i int)
insert into #t select 1
select i from #t
GO
declare @sql nvarchar(max)
declare @TableName nvarchar(max)=''
set @TableName='sysobjects;delete from #t'
SET @SQL='select * from ' + @TableName
exec sp_executesql @SQL, N'@TableName varchar(50)',@TableName

select * from #t

Thanks once again
Glen

(1 row(s) affected)
i
-----------

1

(1 row(s) affected)

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@TableName".
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@TableName".
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@TableName".

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-12 : 23:12:38

declare @TableName nvarchar(max) -----=''

the <=''> is a default value. You can only do that for stored procedure parameters not local variables.

Be One with the Optimizer
TG

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 23:23:55

quote:
I tried the same code but I am getting the following errors below

Are you using SQL 2008 because with sql2005 u cannot do this.

declare @TableName nvarchar(max)=''

Instead do this

declare @TableName nvarchar(max)

set @TableName=''

PBUH

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-11-12 : 23:36:24

Thanks TG and Sashin
I got a lot of information , but I dont think that is what the requirement is in the orginal query.
Still unconvinced, but would like to hear your openion if the answer is appropriate to the Question put at first
-------------------
Thanks TG and Sashin . I cannot understand the where clause WHERE CRMId=21)and what is
CRMID = 21. The code you gave runs well.

DECLARE @TableName VARCHAR(50)
SET @TableName=(SELECT TableName FROM CRMCustomer WHERE CRMId=21)

Here am getting a Table name in a variable.. Table name is coming..

SELECT * FROM @TableName

But when I using the variable name to select the table its not working fine. Asking to Declare the Table variable.

Please help me in this to Select the Table Details from the Selected Variable name.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-13 : 09:07:35

quote:
Originally posted by glendcruz

would like to hear your openion if the answer is appropriate to the Question put at first

It's not clear if you mean YOUR first question or the original poster's first question. But in either case neither of you actually ASKED a question.

The code you posted works for me once I removed the default value set in the local variable: @tableName

Be One with the Optimizer
TG

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources