LINQ to SQL & Stored Procedure return type

Here is my stored procedure:

CREATE PROCEDURE PurgeFoo
AS
  DELETE FROM Foo
  RETURN @@ROWCOUNT

I've added this stored procedure to my DBML, but the Return Type is "(Auto-generated Type)" but I'm an i开发者_如何转开发nt.

What am I doing wrong?

EDIT: Dropped and re-created the store procedure and removed it from the DBML. Now, the return type is "(None")

You could change the return to a SELECT. This way, you get a proper recordset.

CREATE PROCEDURE PurgeFoo
AS
DELETE FROM Foo
SELECT @@ROWCOUNT AS RowsDeleted
GO

Or an OUTPUT parameter, like so:

CREATE PROCEDURE PurgeFoo
  @RowsDeleted int = 0 OUTPUT
AS
DELETE FROM Foo
SET @RowsDeleted =  @@ROWCOUNT
GO

The RETURN value is most useful for status and error codes, not meaningful data.

Why not do a SELECT COUNT(*) FROM Foo to count the rows before you delete them since all rows are deleted? Something like this:

CREATE PROCEDURE PurgeFoo
@Rows INT OUTPUT
AS
SET @Rows = SELECT COUNT(*) FROM Foo
DELETE FROM Foo

Then when you call the SP, you can get value of the output parameter as an INT. Also, you can change the * to which ever column you want to make the SELECT statement run faster.