How to select from database passed as param without dynamic sql?

Question

Post reply

How to select from database passed as param without dynamic sql?

John Mason

SSC Rookie

Points: 48
More actions
January 27, 2005 at 2:35 pm

#60391

Hi,

I have a scenerio were I have various stored proc's which handle inserts, updates etc on around 50 databases. At the moment I am passing the database name as a parameter and then doing the fobidden EXEC (@SQL) statement which executes the dynamic sql statement.

Is there any way of doing this without doing a dynamic sql statement?

e.g. SELECT ID FROM [databasename].[dbo].[Products]

Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

Yes you can! Have a go and you will see!

Kindest Regards,

John Mason SSC Rookie Points: 48 More actions · Answer 2

Hi,

I dont think I can do:

SELECT * FROM [@mydatabasename].[dbo].[Products]

Can I?

Ive been doing:

SELECT @sql = 'SELECT * FROM ['+@mydatabasename+'].[dbo].[Products]'

?

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 3

If you truly need to pass in the db name as a parameter, the only way I know how is to use dynamic sql, either with EXEC or sp_executesql.

Can you tell us a little more about the need to use dynamic database names? Does this change in production, or is it more environment driven, where by environment I mean DEV vs QA vs UAT vs PROD etc?

Scott Thornburg

Volt Information Sciences

TriggerMe SSChampion Points: 11752 More actions · Answer 4

My apologies, I was refering to dynamic sql!

Kindest Regards,

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

If it's a stored procedure and all activites are in one database only, then try removing 3 part (dbname.dbo.table) names from the procedure and using one part names (table) then just set the execution context. I'm assuming you presently say:

exec sp_procedure_name

after the above changes (if possible):

exec database_name..sp_procedure_name

The only fallout that I've found to this method is that some niladic functions will not work properly (return null values).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

noeld SSC Guru Points: 96590 More actions · Answer 6

and adding to rudy_komacsar be aware that your procedure cache will contain numerous execution plans one per database + procedure name combination.

There may be a way to create a partitioned view solution but it will requeried a lot of recoding ( which you may not want )

you could also change database context with Use dbName

but I don't know enough about your app

HTH

* Noel

Joseph Rush SSC Veteran Points: 264 More actions · Answer 7

Unfortunately, you get an error trying the "Use" statement inside a stored procedure, so it looks to me like the only way to do it is via dynamic SQL (bleah).

AC Lupu Say Hey Kid Points: 709 More actions · Answer 8

In the case the SP would have an OUTPUT ...

Any ideas how we could get it's value ,assuming SP call is done through dynamic sql?

AC Lupu Say Hey Kid Points: 709 More actions · Answer 9

Not sure if will work for you,but try to call your SP as follow:

create procedure MainSp ( @PDBName nvarchar(20) )

AS

declare @ProcName nvarchar(20)

set @ProcName = @PDBName + '..YourSp'

exec @ProcName [parameters defined as normal]