pass database name as parameter in SP

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)

pass database name as parameter in SP

Author

Topic

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2009-07-02 : 05:13:31

Hi All,

Is there anyway to pass the database name as parameter in stored procedure. I also want to use this in the cursor something like

DECLARE xDetail CURSOR
FOR SELECT EarnID.EarningValue,EarnID.Period,EarnID.Comment,EarnID.PostingDate,EarnID.SLSubAccountNumber FROM [@NonSLDBName].dbo.SLIntegrationEarnings EarnID
WHERE EarnID.SLCompanyID = @EarnCpnyID AND ISInProcess = 1
OPEN xDetail

Instead of @NonSLDBName in the above I need to pass the database name. If i tried something like above it is throwing error.

Appreciate your help.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 05:15:21

You need dynamic SQL!!!
http://www.sommarskog.se/dynamic_sql.html
or you can put in pleanty if-else statement ^^
AMITOFO+AMEN+YA ALLAH Hope the query works

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 05:17:08

declare @str varchar(max), @dbname varchar(32)

select @dbname = 'databasename'

select @str = 'select * from '+@dbname +'.dbo.tablename'

print @str

exec(@str)

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2009-07-02 : 05:29:03

I also need have the select statment with passing database something like that I mentioned in my example

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2009-07-02 : 10:42:20

Anyone know how to pass database name in the CURSOR.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-02 : 11:25:39

and why do you need this cursor for? may be there is a better way

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2009-07-06 : 03:00:16

Dear Rohit,

Thanks for your reply.

Yes, there can be many better way and I also done. But what is the reason and why I am not able to validate with the passing database name parameter in the cursor.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-06 : 04:02:02

Instead of using cursor, see if you can try something like this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 05:13:25

You can also make use of SYNONYMs.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-06 : 08:21:40

quote:
Originally posted by gurusamy.senthil

Dear Rohit,

Thanks for your reply.

Yes, there can be many better way and I also done. But what is the reason and why I am not able to validate with the passing database name parameter in the cursor.

you cannot use a variable as table name (or part of table name) in the query, it has to be a dynamic query to do that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources