Exec an SQL-Server 2008 stored-procedure from Access, passing a TABLE variable

Question

I need to pass a table from Access to SQL-server and execute a stored-procedure.

I'm using pass-through queries in Access to do this.

My pass-through query:

DECLARE @MyVar TABLE { .....<variables> }

INSERT INTO @MyVar   SELECT *
    FROM [MyTable]

EXEC sproc_test @Myvar

My stored-procedure:

ALTER PROCEDURE [dbo].[sproc_test] 

@MyVar TABLE(....<variables>) 

AS ...<the rest of the sproc>

Should this work? I'm getting an "Incorrect syntax near TABLE" error in the stored-procecure.

Answer 1

To accept a TABLE variable as a parameter to a stored procedure, you need to define a table type and specify that as the type in the sproc instead of defining the type as TABLE in your sproc definition

e.g. you'd end up with something like this

ALTER PROCEDURE [dbo].[sproc_test]
    @MyVar MyTableType READONLY
AS
...

Check out this article for how to define the table type etc.