Pass Database name as Stored Procedure Parameter 1

[Appollo14]

Hi,

I need to be able to set the database name used within a stored procedure by passing a parameter at run time. I know how to pass a parameter normally but I seem to be a bit stuck on this one.

Here is an illustration of how i thought it would work.

create procedure [dbo].[sp_Lock]
@dbExt varchar(10)
Update MyDb_ + @dbExt +.dbo.myTable
set myValue = 1
go

Unfortunately, it doesn't like the syntax that i put in.

Any Ideas?

Regards,
Noel.

 

[monksnake]

I'm pretty sure you have to use the exec() command when building a query out of a string.

<.

 

[monksnake]

sorry, syntax:

exec('Update MyDb_' + @dbExt + '.dbo.myTable set myValue = 1')

<.

 

[Appollo14]

Hi Monksnake,

thanks for the reply. I had'nt even thought of that approach, i thought that i'd just be able to add the parameter and that sql would just concatenate the fields.

Many thanks,
Noel.