Passing database names as parameters

Hi all,
I posted this yesterday (thanks Uri Diamant), but there has to be a better
solution.
I have a SQL 2005 instance with 4 dbs in it. In three databases, the structu
re
is identical (data is from 3 seperate companies). Is it possible to access
any of the three databases, from sprocs in the fourth (master) database,
so I don't have to rewrite all the sprocs three times?
Can I pass the database name to a stored procedure, eg
ALTER PROCEDURE [dbo].[usp_Clients_Get]
@.user_ref varchar(20),
@.database_ref varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
FROM User_Client_Join INNER JOIN
@.database_ref.[Clients] ON [User_Client_Join.Client_Ref] = [Clients.Client_Ref]
WHERE [User_Client_Join].[User_Ref] = @.user_ref
END
I have many queries and I don't want a huge IF...ELSE structure.
Thanks in advance.
JulesJules
Well , you are probably thinjing about dynamic sql. I'm sure you are aware
of downsides of using dynamic sql
alter proc spproc
@.par int,
@.dbname sysname
as
declare @.str as varchar(100)
set @.str=('select * from '+@.dbname+'..orders where orderid='+cast(@.par as
varchar(10)))
exec (@.str)
exec spproc 10248,'northwind'
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
> Hi all,
> I posted this yesterday (thanks Uri Diamant), but there has to be a better
> solution.
> I have a SQL 2005 instance with 4 dbs in it. In three databases, the
> structure is identical (data is from 3 seperate companies). Is it possible
> to access any of the three databases, from sprocs in the fourth (master)
> database, so I don't have to rewrite all the sprocs three times?
> Can I pass the database name to a stored procedure, eg
> ALTER PROCEDURE [dbo].[usp_Clients_Get] @.user_ref varchar(20),
> @.database_ref varchar(50)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
> FROM User_Client_Join INNER JOIN
> @.database_ref.[Clients] ON [User_Client_Join.Client_Ref] =
> [Clients.Client_Ref]
> WHERE [User_Client_Join].[User_Ref] = @.user_ref
> END
> I have many queries and I don't want a huge IF...ELSE structure.
> Thanks in advance.
> Jules
>|||Hi Uri,
The solution you suggest works! What are the downsides of using dynamic sql
in a sproc. If they are the same as in VB, and this is the only way I can
implement this, this will have to do.
Regards
Jules
> Jules
> Well , you are probably thinjing about dynamic sql. I'm sure you are
> aware
> of downsides of using dynamic sql
> alter proc spproc
> @.par int,
> @.dbname sysname
> as
> declare @.str as varchar(100)
> set @.str=('select * from '+@.dbname+'..orders where orderid='+cast(@.par
> as
> varchar(10)))
> exec (@.str)
> exec spproc 10248,'northwind'
> "Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
> news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
>|||Downside is performance wise. Since the SP is dynamic, the SQL Server has to
recompile it every time it is called. The normal SP is compiled once and
stored in the cache so it is much quicker to use. It may not make any
difference for you so give it a shot.
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b91598c81afa7fcca47e@.news.microsoft.com...
> Hi Uri,
> The solution you suggest works! What are the downsides of using dynamic
> sql in a sproc. If they are the same as in VB, and this is the only way I
> can implement this, this will have to do.
> Regards
> Jules
>
>|||Thanks Uri & Grant,
I'd rather do it the non-dynamic way, if is this possible. My client could
be adding more databases in the future.
Regards
Jules
> Downside is performance wise. Since the SP is dynamic, the SQL Server
> has to recompile it every time it is called. The normal SP is compiled
> once and stored in the cache so it is much quicker to use. It may not
> make any difference for you so give it a shot.
> "Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
> news:8e34528b91598c81afa7fcca47e@.news.microsoft.com...
>|||Jules,
I think you can achieve this using dynamic SQL, but you will have to create
the SQL command in a string.
e.g. looking at the first bit of your first line.
DECLARE @.SQL nvarchar(1000)
SET @.SQL = 'SELECT ' + @.Database_ref + ".[Clients.Co_Name], ' + etc.....
EXEC (@.SQL)
Robin Hammond
www.enhanceddatasystems.com
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
> Hi all,
> I posted this yesterday (thanks Uri Diamant), but there has to be a better
> solution.
> I have a SQL 2005 instance with 4 dbs in it. In three databases, the
> structure is identical (data is from 3 seperate companies). Is it possible
> to access any of the three databases, from sprocs in the fourth (master)
> database, so I don't have to rewrite all the sprocs three times?
> Can I pass the database name to a stored procedure, eg
> ALTER PROCEDURE [dbo].[usp_Clients_Get] @.user_ref varchar(20),
> @.database_ref varchar(50)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
> FROM User_Client_Join INNER JOIN
> @.database_ref.[Clients] ON [User_Client_Join.Client_Ref] =
> [Clients.Client_Ref]
> WHERE [User_Client_Join].[User_Ref] = @.user_ref
> END
> I have many queries and I don't want a huge IF...ELSE structure.
> Thanks in advance.
> Jules
>|||Read this to know more about Dynamic SAL
http://www.sommarskog.se/dynamic_sql.html
Madhivanan|||Another option you could use is to make mirror copies of procedures in the
customer database which do most of the logic you are looking to impliment
then simply call the procedure in dynamic code. Make sure to use the
sp_executesql call to call the procedure:
@.sql = 'exec ' + @.database_ref + 'dbo.ClentDataProcedure'
exec sp_executesql @.stmt = @.sql
This could mitigate some of the issues with some dynamic implimentations as
the code on the customer db would not itself be dynamic and using
sp_executesql allows for both stored query plans. In addition you could pas
s
through parameters to the dynmic procedure call (see bol under sp_executesql
for the gory details)
--Tony
"Jules Wensley" wrote:

> Thanks Uri & Grant,
> I'd rather do it the non-dynamic way, if is this possible. My client could
> be adding more databases in the future.
> Regards
> Jules
>
>
>|||On Tue, 21 Mar 2006 12:31:23 +0000 (UTC), Jules Wensley wrote:

> What are the downsides of using dynamic sql
>in a sproc.
http://www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP