Please help ! - 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

Transact-SQL (2000)

Please help !

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2002-11-13 : 11:04:39

Is it possible to access multiple records from a store procedure while using a temporary variable or temporary table in SQL 2000.

For example If I have a store procedure

Crete Procedure testSp
as
Begin

Declare @test_tab table(Test_id varchar(10)) --Declaring the table var

Insert into @test_tab(test_id) values('1')
Insert into @test_tab(test_id) values('2')
Insert into @test_tab(test_id) values('3')
Insert into @test_tab(test_id) values('4')
Select * from @test_tab
End
GO

When I execute this from the asp page it gives me error :-

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Since I have to use a lot of system function like execute etc., I can't do this in sql function. Please help me.

Thanx in advance

Sachin

sachin.samuel@fqsltd.com

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-13 : 11:13:51

Try this

Crete Procedure testSp
as
Begin
Set nocount on
Declare @test_tab table(Test_id varchar(10)) --Declaring the table var

Insert into @test_tab(test_id) values('1')
Insert into @test_tab(test_id) values('2')
Insert into @test_tab(test_id) values('3')
Insert into @test_tab(test_id) values('4')
set nocount off
Select * from @test_tab
End
GO

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2002-11-14 : 00:34:44

Thanx mr_mist, it worked fine. Thanx a lot.

Sachin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources