how to use use <db name> in store procedure

Hi
I am passing database name as a parameter in the stored procedure
I want to create stored procedure in master and want to access the database
systables depending on the input to the stored procedure.
How do I go in that specific database as I can not use "use @.dbname"
in store procedure.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I am passing database name as a parameter in the stored procedure
> I want to create stored procedure in master and want to access the
> database systables depending on the input to the stored procedure.
> How do I go in that specific database as I can not use "use @.dbname"
> in store procedure.
> Thanks
> Mangesh
The easiest way is probably to use dynamic SQL. Just be really careful
of SQL Injection issues. Make sure the procedure does adequate data
validation.
Creating procedures in master is not recommended. For one reason, they
may disappear with service pack installations. I think you're better off
creating a shared database to store all your "shared" procedures.
Microsoft does this in SQL Server with the msdb database. It just
requires using the database name when executing the procedure.
-- Dynamic SQL
Declare @.n nvarchar(1000)
Declare @.db nvarchar(128)
Set @.db = N'[pubs]'
Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
Exec sp_executesql @.n
David Gugick
Imceda Software
www.imceda.com|||Thanks David.
Is there a best practices document you know of for coding in sqlserver?
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Hi
> >
> > I am passing database name as a parameter in the stored procedure
> > I want to create stored procedure in master and want to access the
> > database systables depending on the input to the stored procedure.
> >
> > How do I go in that specific database as I can not use "use @.dbname"
> > in store procedure.
> >
> > Thanks
> > Mangesh
> The easiest way is probably to use dynamic SQL. Just be really careful
> of SQL Injection issues. Make sure the procedure does adequate data
> validation.
> Creating procedures in master is not recommended. For one reason, they
> may disappear with service pack installations. I think you're better off
> creating a shared database to store all your "shared" procedures.
> Microsoft does this in SQL Server with the msdb database. It just
> requires using the database name when executing the procedure.
>
> -- Dynamic SQL
> Declare @.n nvarchar(1000)
> Declare @.db nvarchar(128)
> Set @.db = N'[pubs]'
> Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
> Exec sp_executesql @.n
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>sql