Passing Table Valued parameter to stored procedure across different databases

sql-server stored-procedures table-valued-parameters
hgulyan picture hgulyan · Mar 2, 2012 · Viewed 9k times · Source

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.

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Mar 2, 2012

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

Related questions

How to pass table value parameters to stored procedure from .net code

I have a SQL Server 2005 database. In a few procedures I have table parameters that I pass to a stored proc as an nvarchar (separated by commas) and internally divide into single values. I add it to the SQL command …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More