MS SQL stored procedure returned result sets with ODBC

Question

I have a stored procedure and if the stored procedure does this:

SELECT 0 As Ret
DELETE FROM table where value1 = 1

Returns 1 row result with its value of 0 and column name Ret

But if I do this:

DELETE FROM table where value1 = 1
SELECT 0 As Ret

I get no returned results.

My question is, how do I get the second variation to return a value.

I'm using C++ and ODBC.

Answer 1

Explicitly set a variable to @@RowCount and then return it

Declare @ret int
Select @ret = @@RowCount
Select @ret

EDIT

It's returning 0 for the second form because the row count for the select of 0 as ret is 0 rows. So you'll want to store the number of rows returned immediately after your Delete.

Answer 2

See the setting for - SET NOCOUNT.

Answer 3

shahkalpesh is right, nocount should work for this, though I would recommend not using a result set as a return value and instead using the RETURN statement, and interrogating the return value of the proc.

SET NOCOUNT ON 
DELETE FROM table where value1 = 1
SET NOCOUNT OFF
SELECT 0 As Ret

Answer 4

Ok I found that you can use the ODBC call SQLMoreResults to get the next result set. So you can keep calling this SQLMoreResults function until there are no more result sets left.

In my case after calling SQLMoreResults I got my expected result set.

This is pretty cool because it means that a single stored procedure can return multiple result sets. I never knew that it could.

@Sambo99 and @shakalpesch your suggestions also works and returns only 1 result.