Passing DB Name as a Parameter to Stored Proc

[vness]

Is it possible to pass a database name into a stored procedure as variable to fully qualify an object?

For example:

CREATE PROCEDURE [dbo].[Test_SP]

@p2 as nvarchar(10)

AS

SELECT DISTINCT @p2.dbbjects.Col1, @p2.dbbjects.Col3, @p2.dbbjects.Col4, @p2.dbbjects.ID

FROM @p2.dbbjects INNER JOIN @p2.dbo.Structure ON @p2.dbbjects.ID = @p2.dbo.Structure.ID

WHERE (@p2.dbo.Structure.Col7 = 4 )
GO

 

[sunila7]

Hi,

u will have to use Dynamic SQL to do that....


CREATE PROCEDURE [dbo].[Test_SP]

@p2 as nvarchar(10)

AS
Declare @SQL varchar(2000)

SET @SQL = 'SELECT DISTINCT ' + @p2 + '.dbbjects.Col1, ' + @p2 + '.dbbjects.Col3, ' + @p2+ '.dbbjects.Col4, ' + @p2 + '.dbbjects.ID

FROM ' + @p2 + ' .dbbjects INNER JOIN ' + @p2 + ' .dbo.Structure ON ' + @p2 + '.dbbjects.ID = ' + @p2 + '.dbo.Structure.ID
WHERE (' + @p2 + '.dbo.Structure.Col7 = 4 )'

Exec(@SQL)
GO


Hope it helps

Sunil