SELECT ... FROM [@var1] - SQL Server Forums

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

SELECT ... FROM [@var1]

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-29 : 09:21:06

Hi,

declare @t_name varchar(100)
declare @name varchar(100)
declare @maxdate datetime

set @t_name ='AUD-JPY'
set @name = 'NAUD/JPY'

select @maxdate = MAX(Dates) from [@t_name].dbo.[@name]
PRINT @maxdate

i wanna use variables in the statement above, would you please help?

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 09:22:49

Not possible until you use Dynamic SQL
Why do you want to pass DB and table names as parameters?

Madhivanan

Failing to plan is Planning to fail

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-29 : 09:26:37

set @t_name ='AUD-JPY'
set @name = 'NAUD/JPY'

i will NOT use the lines above, the code segment is the part of a SP and i need to get the DB names into local variables. How can i do it with Dynamically?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 09:32:41

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-29 : 10:08:03

declare @t_name varchar(100)
declare @name varchar(100)
declare @maxdate datetime
declare @SQL varchar(1000)
declare @SQL_INS varchar(1000)

SELECT @SQL = 'select ''@maxdate'' = MAX(Dates) from [AUD-JPY].dbo.[NAUD/JPY]'
EXEC (@SQL)

*** Here it works fine, @maxdate gets the appropriate datetime value but after the statement below,

SELECT @SQL_INS = 'select [Dates],[O] FROM [Q3-2005].dbo.[NAUD/JPY]
WHERE [Dates] > ''@maxdate''
ORDER BY [Dates] ASC'
Exec (@SQL_INS)

**** It gives error, saying error converting datetime from character string. Whats wrong?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-29 : 12:27:51

>> Whats wrong?

SELECT @SQL = 'select ''@maxdate'' = MAX(Dates) from [AUD-JPY].dbo.[NAUD/JPY]'

the above one doesn't assign a value to the variable u defined as @maxdate
can check by a print @maxdate after the
SELECT @SQL = 'select ''@maxdate'' = MAX(Dates) from [AUD-JPY].dbo.[NAUD/JPY]'
EXEC (@SQL)

ur requirement can be fulfilled by

select [Dates],[O] FROM [Q3-2005].dbo.[NAUD/JPY]
WHERE [Dates] > (select MAX(Dates) from [AUD-JPY].dbo.[NAUD/JPY])

or by assigning the date seperately and use that directly.

Just go thru the link provided by Madhi, and understand the Dynamic SQL thoroughly.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources