Varible resolution in SQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Varible resolution in SQL

Author

Topic

akhaware
Starting Member

2 Posts

Posted - 2010-04-19 : 06:20:01

Hi Friends,

I am trying to convert the query written in Oracle 10g, so that it could be run on SQL Server 2005.

In Oracle I run a query as:-
Select * from <user_name>.<table_name>

Same works in SQL Server as:-
Select * from <user_name>..<table_name>

Instead of one dot, there will be 2 dots in SQL Server.

Problem arises when the <user_name> needs to passed through the program.

Let say,
declare @DB_SCHEMA varchar(50);
set @DB_SCHEMA = 'user1';
print @DB_SCHEMA;
select * from @DB_SCHEMA..<table_name>

This gives error - Incorrect syntax near '.'.

Shouldn't @DB_SCHEMA be replaced by the value 'user1' set earlier?

Please let me know the correct way to run this query.

--
Ashish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 06:31:35

nope. for that you need to use dynamic sql like

EXEC('select * from '+ @DB_SCHEMA + '..<table_name>')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 06:48:24

" Instead of one dot, there will be 2 dots in SQL Server"

That will be a different database then, rather than a different schema. Syntax is:

TableName (using the Schema assigned to the currently connected user, or "dbo" by default)
SchemaName.TableName - explicitly name a Schema
DatabaseName.SchemaName.TableName - from a specific database, and Schema - this can be shortened to DatabaseName..TableName for the default
and, finally, ServerName.DatabaseName.SchemaName.TableName to connect to a remote Server.

My vague memory of Oracle is that the syntax is InstanceName.TableName, and an "InstanceName" would correspond to a "DatabaseName" in SQL Server (in which case your 3-part naming is correct), however you have used "<user_name>" in your example - which sounds more like an Owner/Schema in SQL Server

Either way, you cannot use a parameter in a query for any of the 4 parts of the name, as Tara has explained you have to use dynamic SQL for that (which has a number of potential problems and pitfalls)

IF (may be a big "if"!!) you can use SchemaName then:

SELECT * FROM MyTable

(i.e. not explicitly naming a Schema/Owner) will use the Schema/Owner for the currently connected user, so will automatically adjust ... that MIGHT (might be a big "might"!!!) be a solution?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 06:49:07

And validate those strings!

You should read this if you want to go down that route.
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

akhaware
Starting Member

2 Posts

Posted - 2010-04-21 : 05:40:42

Thanks guys, not only the solution provided helped me solving the issue, it has also helped me clearing my doubts.

--
Ashish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources