Use + variable 1

[svagelis]

i want to create a stored procedure that acts in diferent database each time depending on a parameter p.e in pseudo

sp Duplicate_Database (@Databasename as varchar)

use @databasename

select * from tbl_Orders
....
....
....
end

but it doesn work... What is wrong?
thanks in advance.

 

[gny]

I have the same problem. The "Use database" statement isn´t allowed in stored procedures or triggers. Please let me know if you find a solution.

/gny

 

[rajeevnandanmishra]

Hi there,
Sometimes before i had the same problem.
But i use the creation of dynamic sql with database qualifiers to perform the task.
It was a little bit cumbersome, but it works for me.
Like :

Create Procedure myProcedure (@myDataBase as varchar(50))
AS
DECLARE @sql VARCHAR(255)
-- If i have to select data into a temporary table
SELECT @sql="SELECT mycolumn(s) FROM "+@myDataBase+
"..myTable(s)"
CREATE TABLE #myTempTable (Col1 Type, Col2 Type, ...)
INSERT #myTempTable
EXEC(@sql)
-- If i have to update some tables
SELECT @sql="UPDATE "+@myDataBase+"..myTable "+
"SET myCol(s) ..."
Same way deletion also works.


Hope this will move you in right direction.

 

[Cagliostro]

sp Duplicate_Database (@Databasename as varchar)

declare @x varchar(100)
set @x='use ' + @databasename
exec @x

select * from tbl_Orders
....
....
....
end
John Fill

ivfmd@mail.md

 

[rajeevnandanmishra]

Hi john,
If you use USE myDatabase command in a batch, sql will keep the new reference only till the batch is completed.
And when you use EXEC command, sql automatically executes it in batch. So as soon as EXEC command is finished, SQL obtained the original database reference.

This happens for SQL 7.0 and below. I don't know about SQL 2K.

 

[tlbroadbent]

Create the stored procedure that you want to run in the master database with a 'sp_' prefix. When you do this, the SP is a System SP. System SPs can be executed from any database. Execute the SP with using the database name rather than master.

Exec mydb.dbo.sp_Duplicate_Database

Executing with the database name like this temporarily changes the context of the run to that database. This should allow you to easily execute the stored procedure in any database. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[svagelis]

I found it ... it first creates a new copy of my olddatabase, it deletes and updates the some tables in the new database and its inserting a record to a table 'tbl_databases' to keep track for my application databases. Check it out ...

CREATE PROCEDURE up_createNewDatabase (@NewDatabase varchar(20) , @OldDatabase varchar(20) ,@Description varchar(10)) AS

DECLARE @OldDatabaseBackup varchar(40)
DECLARE @OldDatabaseLog varchar(40),@OldDatabaseData varchar(40)
DECLARE @NewDatabaseFullPathData varchar(40),@NewDatabaseFullPathLog varchar(40)


DECLARE @StringToExecute as varchar(2000)

SELECT @OldDatabaseLog = @OldDatabase + '_Log'
SELECT @OldDatabaseData = @OldDatabase + '_Data'

SELECT @OldDatabaseBackup = 'D:\MSSQL7\BACKUP\' + @OldDatabase + '.bak'

SELECT @NewDatabaseFullPathData = 'D:\MSSQL7\DATA\' + @NewDatabase + '_Data.mdf'
SELECT @NewDatabaseFullPathLog = 'D:\MSSQL7\DATA\' + @NewDatabase + '_Log.ldf'

BACKUP DATABASE @OldDatabase
TO DISK = @OldDatabaseBackup

RESTORE FILELISTONLY

FROM DISK = @OldDatabaseBackup

RESTORE DATABASE @NewDatabase

FROM DISK = @OldDatabaseBackup

WITH MOVE @OldDatabaseData TO @NewDatabaseFullPathData,

MOVE @OldDatabaseLog TO @NewDatabaseFullPathLog


Exec ResetDataBase @NewDatabase , 0

IF @@ERROR=0
BEGIN
Select @stringToExecute= 'USE master'+ ' '
Select @stringToExecute= @stringToExecute + 'INSERT INTO TBL_DATABASES '
Select @stringToExecute= @stringToExecute + '(NAME , DESCRIPTION , ISVALID) VALUES (''' +@NewDatabase+''','''+@Description+''',1)'
EXEC (@stringToExecute)
END


-------------------------
CREATE PROCEDURE up_ResetDatabase (@NewDatabase varchar(20) , @StartPoint int) AS
DECLARE @stringToExecute as varchar(500)

SELECT @stringToExecute = 'USE ' + @NewDatabase + ' '
SELECT @stringToExecute = @stringToExecute + 'DELETE FROM TBL_EXTERNAL_ORDERS' + ' '
SELECT @stringToExecute = @stringToExecute + 'DELETE FROM TBL_ORDERS' + ' '
SELECT @stringToExecute = @stringToExecute + 'DELETE FROM TBL_RECEIPTS' + ' '
SELECT @stringToExecute = @stringToExecute + 'DELETE FROM TBL_ADDRESS' + ' '
SELECT @stringToExecute = @stringToExecute + 'DELETE FROM TBL_CUSTOMERS' + ' '
SELECT @stringToExecute = @stringToExecute + 'UPDATE TBL_INDEXES SET LAST_iNDEX = 1' + ' '
SELECT @stringToExecute = @stringToExecute + 'DBCC CHECKIDENT (TBL_ADDRESS, RESEED,' + @StartPoint + '0)' + ' '
SELECT @stringToExecute = @stringToExecute + 'DBCC CHECKIDENT (TBL_CUSTOMERS, RESEED, 0,' + @StartPoint + '0)' + ' '
SELECT @stringToExecute = @stringToExecute + 'DBCC CHECKIDENT (TBL_RECEIPTS, RESEED, 0,' + @StartPoint + '0)'
EXEC (@stringToExecute)