Code: --in db1 context IF EXISTS (SELECT name FROM sysobjects WHERE name = 'test' AND type = 'P') DROP PROCEDURE...
CREATE PROCEDURE dbo.test AS --to create db if not existing IF NOT EXISTS (select name from [master].dbo.sysdatabases where name = 'db2') CREATE DATABASE db2 /* if tabl1 exists in db1 then it is copied into db2 and dropped frm db1 */ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tabl1' and xtype='U') BEGIN SELECT * INTO [db2].dbo.tabl1 FROM tabl1 DROP TABLE tabl1 print 'dropped table' END
When I try to execute this, I get the following error msg:
Server: Msg 2702, Level 16, State 2, Procedure test, Line 10 Database 'db2' does not exist.
How do I get this working?
View questions and answers from all of our SQL Server experts here.
Dig Deeper on SQL Server Stored Procedures
Related Q&A from Greg Low
Should developers be granted permissions to production queue in a SQL Server environment? See why expert Greg Low suggests proc access by WITH ...continue reading
A SQL Server application that needs user permission to create and modify tables in SQL Server 2005 may be handled best via a schema.continue reading
Copying stored procedures between databases on the same SQL Server 2005 can be done through scripting the stored procedures with this method.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.