stored procedure?????????? - 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

General SQL Server Forums

New to SQL Server Programming

stored procedure??????????

Author

Topic

sweet_777
Starting Member

16 Posts

Posted - 2011-01-28 : 00:12:33

Hi all,

in my sp i am passing 2 input parameters,after that i have created one temp table ,in while loop i am insert data into temp table.
i want to return datatable from stored procedure how to do that

but i am not getting output.can you please correct the sp.
my sp is:

alter proc USP_GetGrid
(
@PackTypeCode varchar(100),
@Totalcount varchar(100)
)
as
begin
print @Totalcount
print @PackTypeCode
create table #gridrows(
packtypecode varchar(100),
sequencevalue int,
batch int
)
while (@Totalcount>0)
begin
insert into #gridrows(PackTypeCode,sequencevalue,batch) values(@PackTypeCode,1,1)
end
--end
select * from #gridrows

drop table #gridrows
end

USP_GetGrid 'p01',9
Regards
Rama

Thanks & Regards
Sweet_77

matty
Posting Yak Master

161 Posts

Posted - 2011-01-28 : 00:29:25

The issue is with the while loop.Its never ending.
Why do you need a while loop ? Just check IF @Totalcount > 0

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 00:45:40

in addition to Matty: there are two cases which can occur in the above stored procedure:

1) When @Totalcount is greater than 0 - will result in endLess loop and will execute the insert statement for unlimited number of times.

2) When @Totalcount is not greater than 0 - Will skip the statement of loop block (Begin End) and no insertion would be made into the temp table.

Thus in both cases you would not get any output :)

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources