sql use statement with variable

Question

I m trying to switch the current database with a SQL statement  I have tried the following  but all attempts failed    USE  DatabaseName EXEC sp sqlexec  Sql -- where  Sql    USE       DatabaseName         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

User · Answer

Use exec sp execsql  Sql  Example   DECLARE  sql as nvarchar 100    DECLARE  paraDOB datetime   SET  paraDOB    1 1 1981    SET  sql N SELECT   FROM EmpMast WHERE DOB  gt    paraDOB    exec sp executesql  sql N  paraDOB datetime   paraDOB

User · Answer

I have the same problem  I overcame it with an ugly -- but useful -- set of GOTOs   The reason I call the  script runner  before everything is that I want to hide the complexity and ugly approach from any developer that just wants to work with the actual script  At the same time  I can make sure that the script is run in the two  extensible to three and more  databases in the exact same way   GOTO ScriptRunner  ScriptExecutes   --------------------ACTUAL SCRIPT-------------------- -------- Will be executed in DB1 and in DB2 --------- --TODO  Your script right here  ------------------ACTUAL SCRIPT ENDS-----------------  GOTO ScriptReturns  ScriptRunner      USE DB1     GOTO ScriptExecutes  ScriptReturns      IF  db name      DB1       BEGIN         USE DB2         GOTO ScriptExecutes     END   With this approach you get to keep your variables and SQL Server does not freak out if you happen to go over a DECLARE statement twice

User · Answer

Just wanted to thank KM for his valuable solution  I implemented it myself to reduce the amount of lines in a shrinkdatabase request on SQLServer  Here is my SQL request if it can help anyone    -- Declare the variable to be used DECLARE  Query varchar  1000  DECLARE  MyDBN varchar 11   -- Initializing the  MyDBN variable  possible values   db1  db2  db3       SET  MyDBN    db1   -- Creating the request to execute SET  Query  use     MyDBN     ALTER DATABASE     MyDBN    SET RECOVERY SIMPLE WITH NO WAIT  DBCC SHRINKDATABASE      MyDBN     1  TRUNCATEONLY   ALTER DATABASE     MyDBN    SET RECOVERY FULL WITH NO WAIT  --  EXEC   Query

User · Answer

try this   DECLARE  Query         varchar 1000  DECLARE  DatabaseName  varchar 500   SET  DatabaseName  xyz  SET  Query  SELECT   FROM Server    DatabaseName   Owner Table1  EXEC   Query   SET  DatabaseName  abc  SET  Query  SELECT   FROM Server    DatabaseName   Owner Table2  EXEC   Query

User · Answer

If SQLCMD is an option  it supports scripting variables above and beyond what straight T-SQL can do  For example  http   msdn microsoft com en-us library ms188714 aspx

User · Answer

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

User · Answer

-- If you are using a variable for the database name   -- Try something like this    DECLARE  DBName varchar 50  Set  DBName    Database1       could be passed in by a parameter      IF   DBName    Database1   Begin     USE  Database1   SELECT  FROM Table1  End  IF   DBName    Database2   Begin USE  Database2   SELECT  FROM Table2  End  IF   DBName is null  Begin USE  Database1   End

User · Answer

I case that someone need a solution for this  this is one   if you use a dynamic USE statement all your query need to be dynamic  because it need to be everything in the same context   You can try with SYNONYM   is basically an ALIAS to a specific Table  this SYNONYM is inserted into the sys synonyms table so you have access to it from any context  Look this static statement   CREATE SYNONYM MASTER SCHEMACOLUMNS FOR Master INFORMATION SCHEMA COLUMNS SELECT   FROM MASTER SCHEMACOLUMNS   Now dynamic   DECLARE  SQL VARCHAR 200  DECLARE  CATALOG VARCHAR 200     Master   IF EXISTS SELECT   FROM  sys synonyms s WHERE s name    CURRENT SCHEMACOLUMNS   BEGIN DROP SYNONYM CURRENT SCHEMACOLUMNS END  SELECT  SQL    CREATE SYNONYM CURRENT SCHEMACOLUMNS FOR     CATALOG    INFORMATION SCHEMA COLUMNS   EXEC sp sqlexec  SQL  --Your not dynamic Code SELECT   FROM CURRENT SCHEMACOLUMNS   Now just change  the value of  CATALOG and you will be able to list the same table but from different catalog

User · Answer

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

User · Answer

You can do this   Declare  dbName nvarchar max   SET  dbName    TESTDB    Declare  SQL nvarchar max   select  SQL    USE      dbName      can put command s  here    EXEC   SQL     but not here     This means you can do a recursive select like the following   Declare  dbName nvarchar max   SET  dbName    TESTDB   Declare  SQL nvarchar max    SELECT  SQL    USE      dbName          Select      query here  For XML Path     Type   value  text   1    nvarchar max      Exec   SQL

[sql-server] sql use statement with variable

Examples related to sql-server

Examples related to sql-server-2005

Examples related to dynamic-sql