Menu
I'm trying to switch the current database with a SQL statement. I have tried the following, but all attempts failed:
To add a little more detail.
EDIT: I would like to perform several things on two separate database, where both are configured with a variable. Something like this:
USE Database1 SELECT * FROM Table1 USE Database2 SELECT * FROM Table2 
649
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.
The problem with the former is that what you're doing is USE 'myDB' rather than USE myDB. you're passing a string; but USE is looking for an explicit reference.
The latter example works for me.
declare @sql varchar(20) select @sql = 'USE myDb' EXEC sp_sqlexec @Sql -- also works select @sql = 'USE [myDb]' EXEC sp_sqlexec @Sql 
exec sp_execsql @Sql The DB change only lasts for the time to complete @sql
http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
