How to use database name variable inside a stored procedure?

Question

CREATE PROCEDURE dbo.sp_data_archive
(
    @DataBaseName varchar(100),
)

AS 
BEGIN


    INSERT INTO [@DataBaseName].dbo.TESTTABLE
    INSERT INTO dbo.TestTable

END

Giving error :Invalid object name '@DataBaseName.dbo.TESTTABLE'.

How to solve this?

You might have to use sp_executesql for this. Pass your sql string in executesql and then try t execute it. — Paresh J, Apr 13 '15 at 10:13
Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. — ughai, Apr 13 '15 at 10:21
check this thread http://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query — ughai, Apr 13 '15 at 10:30
@rohitsingh you just have to provide it as a parameter and execute your SQL dynamically. — Kamil Gosciminski, Apr 13 '15 at 10:33
what are you trying to achieve here? `INSERT INTO [@DataBaseName].dbo.TESTTABLE INSERT INTO dbo.TestTable`. is this some sort of pseudo code — ughai, Apr 13 '15 at 10:36
First insert - I'm trying to insert into different database Second Statement - I'm inserting into the current database — rohit singh, Apr 13 '15 at 10:38

score 0 · Answer 1 · answered Apr 13 '15 at 10:40

You should use SP_EXECUTESQL for this purpose

For reference, check the code given below.

alter procedure test_Sp
(
    @DBName1 nvarchar(100),
    @DBName2 nvarchar(100)
)
as
begin

    DECLARE @sql NVARCHAR(500)
    SET @sql = N'select * from ' + @DBName1 + '.sys.tables'
    EXEC SP_EXECUTESQL @SQL                    
    SET @sql = N'select * from ' + @DBName2 + '.sys.tables'
    EXEC SP_EXECUTESQL @SQL

end

How to use database name variable inside a stored procedure?

1 Answers1