Pass a table variable to a stored procedure

Question

Pass a table variable to a stored procedure

TT-131210

SSCommitted

Points: 1603
More actions
May 11, 2011 at 11:14 am

#250572

I have been trying to figure out the correct syntax for passing a table variable to a stored procedure. I was trying the code below but does not work. Any help would be appreciated.
Tim
DECLARE @Temp TABLE (col1 varchar(100), col2 varchar(100))
INSERT INTO @Temp(col1,col2)
SELECT 'ABC', 'XYZ'
EXECUTE My_SP @Temp
...
CREATE PROCEDURE My_SP
(
@Temp TABLE (col1 varchar(100), col2 varchar(100))
)
AS
SELECT * FROM @Temp

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Have you declared a table type matching your table variable definition?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TT-131210 SSCommitted Points: 1603 More actions · Answer 2

No, I am not sure how to do that but I have seen that syntax but I was not sure how that worked. Also, the stored procedure that I will be calling and passing the table variable is on a linked server. Will that be an issue?

Tim

WayneS SSC Guru Points: 95450 More actions · Answer 3

TT-131210 (5/11/2011)
No, I am not sure how to do that but I have seen that syntax but I was not sure how that worked. Also, the stored procedure that I will be calling and passing the table variable is on a linked server. Will that be an issue?
Tim

See this BOL entry for how to do it (build type, use in proc, call proc).

You might need the table type on both servers - please follow up here and let us know how it worked for you (I for one am interested in this).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 4

Both would also probably need to be SQL 2008 or higher too although I haven't tested that.

Eddie Wuerch SSChampion Points: 12533 More actions · Answer 5

TT-131210 (5/11/2011)
[...] Also, the stored procedure that I will be calling and passing the table variable is on a linked server. Will that be an issue?
Tim

That will be an issue. It isn't possible to send a table-valued parameter to a procedure over a linked server, because it works by using a user-defined type (of type table). Even if you declare the same type in both the local and remote databases, the call still isn't possible - the local context is unaware of the remote type.

Just got back to my hotel room from SQL Rally and I'm already answering questions based on what I learned today. Neat. 🙂

-Eddie

Eddie Wuerch
MCM: SQL

TT-131210 SSCommitted Points: 1603 More actions · Answer 6

It looks like I am going to have to come up with a different solution.

Thanks for all the replies.

Tim