T SQL Query for sql server

Question

1.00/5 (1 vote)

See more:

Dear All,

I have two Database
1.ta
2.Theorem_Analytics_database.

Single Stored Procedure

Create procedure MultDB
as
select * from ta.dbo.user_master

declare @ScndDB varchar(100);
set @ScndDB ='[Theorem_Analytics_database].dbo.User_Master';
select * from @ScndDB

ERROR:Must declare the table variable "@ScndDB".

Please help me.

but if it is like below
Create procedure MultDB
as
select * from ta.dbo.user_master

declare @ScndDB varchar(100);
set @ScndDB ='[Theorem_Analytics_database].dbo.User_Master';
select @ScndDB

Execution Success.

it displays
all data of ta DB User_Master Table.
and
[Theorem_Analytics_database].dbo.User_Master

Posted 25-Jul-13 21:38pm

connect2manas

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-07-25T21:43:00

Solution 1

You can't use a variable as the source of a table name in SQL: you would have to create a string containing the full SQL satement and then execute it:

SQL

SET @ScndDB ='[Theorem_Analytics_database].dbo.User_Master';
DECLARE @Command VARCHAR(MAX)
SET @Command='SELECT * FROM ' +@ScndDB
EXEC @Command

Posted 25-Jul-13 21:43pm

OriginalGriff

Comments

connect2manas 26-Jul-13 3:59am

Hi,
Thanks for reply.
please help me.

Now my Procedure Becomes like below:

Alter procedure MultDB
as
select * from ta.dbo.user_master

declare @ScndDB varchar(100);
set @ScndDB ='[Theorem_Analytics_database].dbo.User_Master';

DECLARE @Command VARCHAR(MAX)
SET @Command='SELECT * FROM ' +@ScndDB
EXEC @Command

Command(s) completed successfully.

Exec MultDB;
First Table data displaying only.
for second error

OriginalGriff 26-Jul-13 4:09am

:doh:
It's early - I forgot the brackets...

EXEC(@Command)

connect2manas 26-Jul-13 5:25am

thanks ,its coming.
Sir if i want to get particular fields of @ScndDb Table,then what i have to do?
please reply me.

OriginalGriff 26-Jul-13 5:32am

Replace the "*" with the list of field names.

connect2manas 26-Jul-13 5:45am

Alter procedure MultDB
as
select * from ta.dbo.user_master

declare @ScndDB varchar(100);
declare @sql varchar(MAX);

set @ScndDB ='[Theorem_Analytics_database].dbo.User_Master';

SET @SQL='SELECT User_Email,First_Name,Last_Name FROM '+@ScndDB

EXEC (@SQL)

Now i want to store the User_Email,First_Name,Last_Name in different varible.
plz help me.

OriginalGriff 26-Jul-13 6:38am

This is where it starts to get extremely nasty, and you have to start thinking "Have I gone down the wrong route? Would I be better off redesigning this?" because a SELECT does not return a single value, or (in your case) even a single row. It returns a table of values, so you have to start looking at doing this with VIEWs or globally accessible temporary tables, which is a level of nastiness that you probably don't want to go anywhere near if you can help it! :laugh:

You can do it if you want:
http://stackoverflow.com/questions/9534990/tsql-select-into-temp-table-from-dynamic-sql
but it's not a lot of fun, and you are probably better off not going there...