11

I'm using SQL Server 2008.

How can I pass Table Valued parameter to a Stored procedure across different Databases, but same server?

Should I create the same table type in both databases?

Please, give an example or a link according to the problem.

Thanks for any kind of help.

hgulyan
  • 8,099
  • 8
  • 50
  • 75
  • I don't believe it's possible - you can't reference a table type from another database, and even with identical type definitions in both DBs, a value of one type isn't assignable to the other. – Damien_The_Unbeliever Mar 02 '12 at 10:55
  • What choice do I have in this situation? Using XML type? – hgulyan Mar 02 '12 at 10:58

3 Answers3

11

In response to this comment (if I'm correct and that using TVPs between databases isn't possible):

What choice do I have in this situation? Using XML type?

The purist approach would be to say that if both databases are working with the same data, they ought to be merged into a single database. The pragmatist realizes that this isn't always possible - but since you can obviously change both the caller and callee, maybe just use a temp table that both stored procs know about.

I don't believe it's possible - you can't reference a table type from another database, and even with identical type definitions in both DBs, a value of one type isn't assignable to the other.

You don't pass the temp table between databases. A temp table is always stored in tempdb, and is accessible to your connection, so long as the connection is open and the temp table isn't dropped.

So, you create the temp table in the caller:

CREATE TABLE #Values (ID int not null,ColA varchar(10) not null)
INSERT INTO #Values (ID,ColA)
/* Whatever you do to populate the table */
EXEC OtherDB..OtherProc

And then in the callee:

CREATE PROCEDURE OtherProc
/* No parameter passed */
AS
    SELECT * from #Values
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

Table UDTs are only valid for stored procs within the same database.

So yes you would have to create the type on each server and reference it in the stored procs - e.g. just run the first part of this example in both DBs http://msdn.microsoft.com/en-us/library/bb510489.aspx.

If you don't need the efficency you can always use other methods - i.e. pass an xml document parameter or have the s.p. expect a temp table with the input data.

Edit: added example

create database Test1
create database Test2
go
use Test1
create type PersonalMessage as TABLE
(Message varchar(50))
go
create proc InsertPersonalMessage @Message PersonalMessage READONLY AS
  select * from @Message
go
use Test2
create type PersonalMessage as TABLE
(Message varchar(50))
go
create proc InsertPersonalMessage @Message PersonalMessage READONLY AS
  select * from @Message
go
use Test1
declare @mymsg PersonalMessage
insert @mymsg select 'oh noes'
exec InsertPersonalMessage @mymsg 
go
use Test2
declare @mymsg2 PersonalMessage
insert @mymsg2 select 'oh noes'
exec InsertPersonalMessage @mymsg2

Disadvantage is that there are two copies of the data. But you would be able to run the batch against each database simultaneously. Whether this is any better than using a table table is really down to what processing/data sizes you have - btw to use a temp table from an s.p. you just access it from the s.p. code (and it fails if it doesn't exist).

Peter Wishart
  • 11,600
  • 1
  • 26
  • 45
  • It doesn't work. I've got this error "Operand type clash: PersonalMessages is incompatible with PersonalMessages" – hgulyan Mar 02 '12 at 10:58
  • Yes one restriction is that when you declare the table, it uses the datatype bound to the current database context. So you'd have to do: "use db1", create table and call proc, go, "use db2" etc. You could switch to passing a temp table to the s.p. or populate a global temp table, then pass via a separate table variable for each db. – Peter Wishart Mar 02 '12 at 11:16
  • but the question is in using table type between two databases. – hgulyan Mar 02 '12 at 11:17
  • I've tried the same thing, but that's not what I need. I need to exec stored in test2 from test1 db. Something like declare mymsg PersonalMessage insert mymsg select 'oh noes' exec test2.dbo.InsertPersonalMessage mymsg – hgulyan Mar 02 '12 at 11:37
  • Aha, yes so you'd need to change the database context to test2 before declating the message. Sounds like temp tables are a better solution! – Peter Wishart Mar 02 '12 at 11:41
0

Another way to solve this (though not necessarily the correct way) is to only utilize the UDT as a part of a dynamic SQL call.

USE [db1]
CREATE PROCEDURE [dbo].[sp_Db2Data_Sync] 
AS
BEGIN
 /*
 *
 * Presumably, you have some other logic here that requires this sproc to live in db1.
 * Maybe it's how you get your identifier?
 *
 */
 
 DECLARE @SQL VARCHAR(MAX) = '
  USE [db2]
  DECLARE @db2tvp tableType
  
  INSERT INTO @db2tvp
  SELECT dataColumn1
  FROM  db2.dbo.tblData td
  WHERE td.Id = ' + CAST(@YourIdentifierHere AS VARCHAR) '

  EXEC db2.dbo.sp_BulkData_Sync @db2tvp
'
  EXEC(@SQL)

END

It's definitely not a purist approach, and it doesn't work for every use case, but it is technically an option.

Xellarant
  • 77
  • 1
  • 10