SQL Server: is it possible to use the select @var=field from table syntax with a variable database?

Question

When I use the script below, everything works fine :

 create table MyTable (MyField varchar(5))

 insert into Mytable values ('XXX')

 declare @MyVar varchar(5)

 select @MyVar = MyField 
 from dbo.MyTable

 print @MyVar

But what I would like to do is something like that :

 create table MyTable (MyField varchar(5))

 insert into Mytable values ('XXX')

 declare @MyVar varchar(5)

 declare @DataBase varchar(10) = 'DBMyBase'

 select @MyVar = MyField 
 from @DataBase.dbo.MyTable

 print @MyVar

I tried with no success :

 create table MyTable (MyField varchar(5))

 insert into Mytable values ('XXX')

 declare @MyVar varchar(5)
 declare @DataBase varchar(10) = 'DBMyBase'
 declare @cmd varchar(max)

 set @cmd = 'select @MyVar = MyField from ' + @DataBase + '.dbo.MyTable'

 exec(@cmd)

 print @MyVar

I also tried to put the whole script in @cmd, but no way to catch @MyVar value outside the part of script...

Sorry for I don't know how to explain it in English, but thanks for any help if you understood me !

thx all for your help, I didn't know sp_executesql... – Christophe Nov 20 '19 at 09:29 — Christophe, Nov 20 '19 at 09:29

score 3 · Answer 1 · answered Nov 19 '19 at 14:35

3

There is no need for dynamic SQL here.

EXEC @some_variable (without parentheses) expects a @module_name_var parameter that can be a three part name.

So you can use a static SQL string and a three part name for sp_executesql to set the database context.

DECLARE @DataBase VARCHAR(10) = 'DBMyBase'
DECLARE @qualified_sp_executesql NVARCHAR(150) = QUOTENAME(@DataBase) + '.sys.sp_executesql'

EXEC @qualified_sp_executesql
  N'select @MyVar = MyField  from dbo.MyTable',
  N'@MyVar nvarchar(60) out',
  @MyVar OUT;

SELECT @MyVar

answered Nov 19 '19 at 14:35

Martin Smith

438,706
87
741
845

I like this approach but I don't see how you can say this isn't dynamic sql. It absolutely is dynamic sql. You are calling EXEC and sp_executesql. It is however very safe dynamic sql. – Sean Lange Nov 19 '19 at 14:45
1

The SQL statement is static rather than dynamic (built up at runtime) – Martin Smith Nov 19 '19 at 14:46
I must be missing something. How is this not built up at runtime? You have the database name in a variable and you append that to the rest of the string. – Sean Lange Nov 19 '19 at 14:48
The SQL statement that is executed is static so no dynamic SQL from my POV. It is always `select @MyVar = MyField from dbo.MyTable` The module name is defined dynamically but that isn't dynamic SQL IMO. Crucially it will only ever be interpreted as a module name what ever is put into it so can't be used to execute arbitrary code – Martin Smith Nov 19 '19 at 14:51
I see what you are saying. As always a well deserved +1 from me. :) – Sean Lange Nov 19 '19 at 14:58
1

thanks. And just in case not clear to anyone else if somehow `@qualified_sp_executesql` got set to `DROP TABLE Student;` then worst that would happen is error `Could not find stored procedure 'DROP TABLE Student;'.` – Martin Smith Nov 19 '19 at 15:00

Mx.Wolf · Answer 2 · 2019-11-19T14:31:02.593

2

When you do not know the whole statement at design time you have to use dynamic SQL like so:

  DECLARE @DataBase NVARCHAR(80) = N'Adventureworks'
  DECLARE @QtdDataBase sysname = QUOTENAME (@DataBase);
  DECLARE @MyVar NVARCHAR(60);
  DECLARE @AddressID INT = 9;
  IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name= @DataBase)
  begin
    DECLARE 
      @sql NVARCHAR(4000) = 'select @MyVar=AddressLine1 from ' 
          + @QtdDataBase 
          + '.SalesLT.Address where AddressID=@AddressID'
    EXEC sys.sp_executesql 
      @sql, 
      N'@AddressID int, @MyVar nvarchar(60) out', 
      @AddressID, 
      @MyVar OUT;
  end
  SELECT @MyVar [@MyVar], @sql [@sql], @QtdDataBase [@QtdDataBase]

The procedure sys.sp_executesql accepts second parameter with description of formal arguments and next parameters used to supply the values for them and receive the output results

edited Nov 19 '19 at 14:31

answered Nov 19 '19 at 13:42

Mx.Wolf

578
5
13

1

`@Database` still needs to be **safely** injected; use `QUOTENAME`. Otherwise this is just a huge injection issue. – Thom A Nov 19 '19 at 14:10
The question was not about security. it was about methods, was it? the whole dynamic sql approach is not good. I agree. So, the comment is ok. My correction is here. – Mx.Wolf Nov 19 '19 at 14:17
1

Just because the OP has a gaping security issue in their code does not mean you should leave it there. Attitudes like that are why SQL injection is still such a problem even now, in an industry that should know far far better... If a friend asked you to pop over and water their plants while they were on holiday, and you noticed their front door was unlocked and wide open when you got there, would you leave it unlocked when you left? No, you wouldn't. So why leave the door open and unlocked here? – Thom A Nov 19 '19 at 14:21
2

Using quotename here is more than just preventing sql injection (but that is extremely important). If you have a space (or some other stupid character) in your database name and you don't use quotename this will fail. – Sean Lange Nov 19 '19 at 14:22
There is no need for dynamic SQL here – Martin Smith Nov 19 '19 at 14:22
No there is no need – Martin Smith Nov 19 '19 at 14:24
So how else do you query an object in a database that is supplied a parameter then, @MartinSmith . AS you're aware `SELECT * FROM @Database.dbo.MyTable;` doesn't work. – Thom A Nov 19 '19 at 14:26
1

`EXEC` accepts a variable that can be the object name to execute and this can be a three part name so `dbname.sys.sp_executesql` – Martin Smith Nov 19 '19 at 14:27
Ahh, yes, I see where you're going with that, @MartinSmith . I forget that functionality exists, in honesty. You should post that as a complete answer. Avoiding the dynamic SQL here would be the best route. – Thom A Nov 19 '19 at 14:29

SQL Server: is it possible to use the select @var=field from table syntax with a variable database?

2 Answers2