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?
- 1You might have to use sp_executesql for this. Pass your sql string in executesql and then try t execute it.Paresh J– Paresh J2015-04-13 10:13:14 +00:00Commented Apr 13, 2015 at 10:13
- can you provide an example?rohit singh– rohit singh2015-04-13 10:14:22 +00:00Commented Apr 13, 2015 at 10:14
- Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures.ughai– ughai2015-04-13 10:21:08 +00:00Commented Apr 13, 2015 at 10:21
- Thanks for the info and what about the real problem?rohit singh– rohit singh2015-04-13 10:22:52 +00:00Commented Apr 13, 2015 at 10:22
- check this thread stackoverflow.com/questions/20678725/…ughai– ughai2015-04-13 10:30:15 +00:00Commented Apr 13, 2015 at 10:30
| Show 4 more comments
1 Answer
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 