sp_executesql with user defined table type not working with two databases

Question

I'm trying to use variables from different databases (hier called Database1 and Database2) and to execute sp_executesql statement.

 USE Database1
 DECLARE @testVariable BIT = 1

 USE Database2
 DECLARE @Param1 t_ID
 DECLARE @Param2 t_ID
 DECLARE @Param3 VARCHAR(100) = '01.01.2010'
 DECLARE @Param4 VARCHAR(100) = '31.12.2015'
 DECLARE @Param5 VARCHAR(100) = '1'
 DECLARE @Param6 VARCHAR(100) = 'EUR'
 DECLARE @ParamDefinition  NVARCHAR(1000) = ''
 DECLARE @SQLString NVARCHAR(1000) = ''
 DECLARE @SQLObject VARCHAR(100) = ''
 INSERT INTO @Param1 VALUES (1) ,(345), (123)
 INSERT INTO @Param2 VALUES (106)

 SET @SQLObject = 'Database2.dbo.Testfunction2'
 SET @SQLString =  'SELECT * FROM ' + @SQLObject + '(@Param1, @Param2,  @Param3, @Param4, @Param5, @Param6)'
 SET @ParamDefinition = '@Param1 t_ID READONLY, @Param2 t_ID READONLY, @Param3 VARCHAR(100), @Param4 VARCHAR(100), @Param5 VARCHAR(100), @Param6  VARCHAR(100)'

 IF @testVariable = 1
 BEGIN
       EXECUTE sp_executesql  @SQLString, @ParamDefinition, @Param1, @Param2, @Param3, @Param4, @Param5, @Param6
 END

In mysp_executesql statement I have also two Variables with user defined table types @Param1 t_ID and @Param2 t_ID. This Type is created in both Databases. If I comment --USE Database1 and run the code above, so that I stay within only one DB (Database2) for the whole process, there is no error, but when I try to run the same procedure with using of two Databases: USE Database1 and USE Database2, I get a compatibility error:

Msg 206, Level 16, State 2, Procedure PROC_Test, Line 0

Operand type clash: t_ID is incompatible with t_ID

What am I doing wrong? Is there any problem solving?

What is the definition of t_ID, and what is the purpose of the first USE statement? — Tab Alleman, Feb 10 '16 at 14:34
This Type is only to get ids: `CREATE TYPE [dbo].[t_ID] AS TABLE( [id] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) )` and actually this sp_executesql statement muss run in this first USE --> in `Database1` — Kateryna Pryshchepa, Feb 10 '16 at 14:44
Variables have scope of the current batch. They are not database specific. — Tom H, Feb 10 '16 at 14:59
Is `Testtable2` actually a table or is it a very poorly named function? http://stackoverflow.com/help/how-to-ask — Tom H, Feb 10 '16 at 15:05

sp_executesql with user defined table type not working with two databases

0 Answers0