CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008stored-procedures
jigs
jigs

Reputation: 13

How to pass database name into stored procedure?

How to pass database name into stored procedure? I tried something like

create procedure dbo.Test
    @databaseName varchar(100)
as
select * from @databasename.Person.Address
go

I would like to use it like this

execute dbo.Test @databaseName = 'AdventureWorks'

Upvotes: 1

Views: 3956

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

Sounds like you are trying to move information which should be part of connection string into stored procedure logic. If you expect that Person table will be in different database this database name should be fixed in deployment - for example by parametrized creation script and sqlcmd.

Upvotes: 1

Oded
Oded

Reputation: 499012

This is not possible in the manner you are describing.

You can do this with dynamic SQL, but that brings its own set of problems.

Upvotes: 4

Related Questions