select * and output 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

Development Tools

ASP.NET

select * and output parameter

Author

Topic

chadchad
Starting Member

3 Posts

Posted - 2008-02-16 : 17:13:50

Hi All

I have a quick question.

This is a test proc that I have set up.

The purpose is to bind the output to a repeater and use the count to organise paging.

How do I get the output for the select * to bind it and also read the count output parameter within the same query?
The @count in the UI will be for example employees total employees = 100

create procedure sp_test
(@name as varchar(20),
@count as int OUTPUT)
as
SELECT *
from tblemployees
where name = @name

select @count =
(select count(*)
from tblemployees)

The problem in asp.net using vb, is that I am confused as how to read the select * and bind that and read the @count as an output parameter. I dont really want to hit the db twice in 2 trips.

Can you please help?

Looking forward to your reply.

Regards
Chad

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-16 : 17:39:46

create procedure sp_test
(@name as varchar(20),
@count as int OUTPUT)
as
SELECT *
from tblemployees
where name = @name

set @count = @@rowcount

E 12°55'05.25"
N 56°04'39.16"

chadchad
Starting Member

3 Posts

Posted - 2008-02-16 : 19:34:12

Hi Peso

Thank you for your answer but I must be more specific in the future.

Its the ado.net and the sql command code that I was after.

I need to return the subset of data to bind and also need to read the total of all the records.(Not the record count of sub data).

georgev
Posting Yak Master

122 Posts

Posted - 2008-02-17 : 11:11:59

Seems to me that your choices are to either run both or run the first (select all) and depending on your method of opening your recordset you could utilize the .RecordCount property

George
<3Engaged!

chadchad
Starting Member

3 Posts

Posted - 2008-02-22 : 05:08:13

Yep, have to go with the 2.

executescalar would be best choice when returning count.

http://aspnet101.com/aspnet101/tutorials.aspx?id=18

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources