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.

AdaTheDev · Accepted Answer · 2010-10-18 10:37:09Z

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.

answered Oct 18, 2010 at 10:37

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related