CODEWITHSUNDEEP
c++sqlsql-serverstored-proceduresodbc
Brian R. Bondy
Brian R. Bondy

Reputation: 347336

MS SQL stored procedure returned result sets with ODBC

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.

Upvotes: 0

Views: 2648

Answers (4)

Brian R. Bondy
Brian R. Bondy

Reputation: 347336

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.

Upvotes: 0

Sam Saffron
Sam Saffron

Reputation: 131112

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

Upvotes: 1

cmsjr
cmsjr

Reputation: 59205

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.

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

See the setting for - SET NOCOUNT.

Upvotes: 3

Related Questions