Where am i doing wrong? - 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)

Where am i doing wrong?

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-03-17 : 02:20:36

All,

Small question about a stored procedure....

here is the situation..i have two databases abc and xyz...now i have one table in each database with the same name ppp.

i wanted to create a stored procedure to pass a variable with one of the database...

CREATE PROCEDURE sp_selectEverything
(
@dbname VARCHAR(50)
)

AS
BEGIN
SELECT * from @dbname.dbo.ppp
END

GO

but i am getting an error saying Message 102, Level 15 and State 1..Please let me know where i am doing wrong?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-17 : 03:35:12

One way:
Use dynamic sql but again it got its own pros and cons.

Second way:

If @dbname = 'xyz'
Select * from xyz.dbo.yourtable
else if @dbname ='abc'
Select * from abc.dbo.yourtable

Note: I have not done testing but I feel it should work.

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-03-17 : 04:38:02

Try this once...

CREATE PROCEDURE sp_selectEverything
(
@dbname VARCHAR(50)
)
AS
declare @sql varchar(max)
set @sql='
SELECT * from '+@dbname+'.dbo.ppp'
exec (@sql)

exec sp_selectEverything 'xyz'

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-17 : 08:41:24

If you go the dynamic SQL route, do some reading first on SQL Injection.

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-18 : 03:52:20

Make sure to read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-18 : 05:25:47

I have no idea why you would want to do this. I'm guessing it's probably not a good idea. Dynamic SQL is best avoided if possible.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-18 : 06:40:08

Not necessarily. There are good use-cases for it. This is not one of them though.

--
Gail Shaw
SQL Server MVP

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-03-18 : 09:24:39

also don't prefix procs with "sp_"

that prefix has special meaning - should be reserved for procs that live in master only.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources