0

I have 2 databases "Alfha" and "Betha".

There is proc_test created in DB "Betha".

It uses table valued parameter of user created type type_test created in db "Betha".

I don't want and I can't have them in db "Alpha".

All my codes HAS TO BE executed over "Alfha" db.

In theory I could put "use betha" at the begining of my code, but I can't do it at the beginning (my code is executed by the SP that adds variable declarations at the beginning)

--use Betha --this works
declare @sql varchar(max)
use Betha --this doesn't work
declare @table_var type_test
execute proc_test @table_var
use Alpha

I use SQL Server 2008 R2.

How to make the code to be compiled? Why "use data" after declaration doesn't exist by compiler?

Machavity
  • 30,841
  • 27
  • 92
  • 100
Look
  • 1
  • 1

2 Answers2

0

See https://msdn.microsoft.com/en-us/library/ms178069.aspx

It's not possible to call the data type out of the database context.

"Use" breaks because it is a batch seperator.

simon_dmorias
  • 2,343
  • 3
  • 19
  • 33
0

I have one workaround It would be:

--use Betha --this works
declare @sql varchar(max)
use Betha --this doesn't work
set @sql= 'use Betha
declare @table_var type_test
execute proc_test @table_var'
execute (@sql)
use Alpha

I'm not very happy about that workaround, but it works.

Problem is that all my code would has to be in @sql

Any other ideas?

Look
  • 1
  • 1