Table variable as a parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table variable as a parameter

Author

Topic

devsql
Starting Member

1 Post

Posted - 2005-11-25 : 01:03:24

Hi,

I'm unable to send or receive table variables as parameters to a stored proceudure. It gives syntax error during the compilation error. The error is "Incorrect syntax near the keyword 'table'". I have given below the sample code.

create procedure OutputTable(@tblTest table (f1 int, f2 int, f3 int) output)
as

--declare @tblTest table (f1 int, f2 int, f3 int)

insert into @tblTest(f1, f2, f3)
values(1, 2, 3)
GO

create procedure InputTable(@tblTest table (f1 int, f2 int, f3 int))
as

select * from @tblTest
GO

Kindly help me out, thank you.

Regards,
Deva

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 01:17:32

I think you cannot use Table variable as Parameter
Declare table variable inside the procedure and select data from that

create procedure OutputTable
as
declare @tblTest table (f1 int, f2 int, f3 int)
insert into @tblTest(f1, f2, f3)
values(1, 2, 3)
Select * from @tblTest
GO

EXEC OutputTable

Otherwise you need to use Function which returns Table

Madhivanan

Failing to plan is Planning to fail

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-06 : 18:00:37

quote:
Originally posted by madhivanan

I think you cannot use Table variable as Parameter
Declare table variable inside the procedure and select data from that

I just posted a similar question. I am trying to capture a table as an input parameter. Is that possible in an sProc or UDF?

I am trying to pass a data table from asp.net to an sProc/UDF. How could it be done if not via a parameter? Should asp.net create a global temp table (can it?) and my sProc work with it or load the data from it into a table variable?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 01:33:33

>>I am trying to capture a table as an input parameter.

Instead, cant you use that table inside the procedure using join?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources