How to use database name variable inside a stored procedure? [duplicate]

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
– Paresh J, Commented Apr 13, 2015 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
– ughai, Commented Apr 13, 2015 at 10:21

Ambareesh Surendran · Accepted Answer · 2015-04-13 10:40:35Z

0

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

answered Apr 13, 2015 at 10:40

Ambareesh Surendran

5082 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use database name variable inside a stored procedure? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related