Get row count and data set from sproc

[RCoate]

I am currently using a sproc like the one below to get both a dataset and a count of the rows returned. Is there a better way to do this?

@Filter		int,
@theRows	int output

AS
BEGIN
SELECT
Field1,
Field2
FROM
MyTable
WHERE
FilterField=@Filter

SET @theRows=(select count(\*) from MyTable WHERE FilterField=@Filter)

END

Thanks.

[Ashfield]

Try this

@Filter		int,	@theRows	int output

AS
BEGIN
SELECT Field1, Field2
FROM MyTable
WHERE FilterField=@Filter

SELECT @theRows = @@ROWCOUNT
END

Bob Ashfield Consultants Ltd

[RCoate]

I knew there was a better way than doing another select/from Thanks Bob.

[Ashfield]

No problem.

Bob Ashfield Consultants Ltd

[Niraj_Silver]

Hi.. Sure You Can Have This One....

@Filter int,
@theRows int output
AS
BEGIN
SET @theRows=(SELECT Count(Field)
FROM MyTable
WHERE FilterField=@Filter)
END

Hope It Will work... do Reply.. Have Nice Day.. :-D