May 28, 2003 at 8:20 pm
Hi All,
I've been trying my darndness to pass a database name as a parameter to Stored Procedure.
My last idead is to pass @dbname sysname to the proc. I'm using he pubs db.
____________________________________________ SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure GetAuth (@dbname sysname)
as
select * from [@dbname].[dbo].[authors]
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
___________________________________________
it compiles fine, but on execution I get
__________________________________________
EXEC [pubs].[dbo].[GetAuth] @dbname='pubs'
__________________________________________
Server: Msg 208, Level 16, State 1, Procedure GetAuth, Line 4
Invalid object name '@dbname.dbo.authors'.
___________________________________________
Any tips on how to do this?
Thanks
May 29, 2003 at 4:49 am
This is one of those situations where a variable cannot be used this way. You can however build a Dynamic SQL string to do this.
CREATE procedure GetAuth (@dbname sysname)
as
EXEC ('select * from [' + @dbname + '].[dbo].[authors]')
May 29, 2003 at 6:03 am
Agree. The way to avoid is to put the proc in each db (if you can) and when you execute, fully qualify the proc - dbname.owner.procname.
Andy
May 29, 2003 at 8:10 am
Thanks for the tip, it worked fine.
One other question, then. How and why do you use SYSNAME in sprocs?
Thanks
May 29, 2003 at 8:10 am
Thanks for the tip, it worked fine.
One other question, then. How and why do you use SYSNAME in sprocs?
Thanks
May 29, 2003 at 8:10 am
Thanks for the tip, it worked fine.
One other question, then. How and why do you use SYSNAME in sprocs?
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply