Passing DBNAME as stored procedure parameter

[yorge]

Hi Guys,
Is there anyway to pass the database name as parameter to a stored procedure?
Kindly refer to the script below. Parameter @chk_db will contain the DB name to use.

TIA,
Yorge

create procedure check_tbl @chk_db nvarchar(50) as

begin
-- Create if exist. Create if not found.
if not exists (select * from [@chk_db].sys.objects
where object_id = object_id(N'[@chk_db].[dbo].[ARC_TBL]') AND type in (N'U'))

begin
create table [@chk_db].[dbo].[ARC_TBL]([LogId] [int] NOT NULL,
[Module] [varchar](64) NOT NULL,
[SSN] [varchar](32) NOT NULL ) on [PRIMARY]


end
else begin
insert into [@chk_db].[dbo].[ARC_TBL]
select LogId
, Module
, SSN
from [MyTable].[dbo].[TRN_LOG]

end
go

 

[bborissov]

You could pass it, but after that you should use so called dynamic SQL to run it:

begin
    -- Create if exist. Create if not found.
    DECLARE @sql nvarchar(max)
    SET @sql = 'if not exists (select * from ['+@chk_db+'].sys.objects
               where object_id = object_id(N''['+@chk_db+'].[dbo].[ARC_TBL]'') AND type in (N''U''))
               begin
                    create table ['+@chk_db+'].[dbo].[ARC_TBL]([LogId] [int] NOT NULL,
                                                               [Module] [varchar](64) NOT NULL,
                                                               [SSN] [varchar](32) NOT NULL ) on [PRIMARY]
               end
            else
               begin
                     insert into ['+@chk_db+'].[dbo].[ARC_TBL]
                     select LogId
                          , Module
                          , SSN
                     from [MyTable].[dbo].[TRN_LOG]
               end'
    EXEC sp_executesql @sql
end

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server