Confusion in calling store procedure from Aspx vs Sql Query Analyzer

[ehx6]

hi, I am really reached a dead end where I could not understand what is going on.
I have created a store procedure (sp)like this:
create procedure p_update as
update employee set salary = 100 where category ='sales'

then tried to call the sp from aspx page and from Sql Query Analyzer, I got different results

Calling the sp from aspx page :
----------------------------------
..... previous code to connect to DB
Dim strSQL as String
strSQL = "p_update"
Dim objCmd as New SqlCommand(strSQL , CON )
Dim rowsAffected As Integer
rowsAffected = objCmd.ExecuteNonQuery
if rowsAffected = 0 then
response.write ("No Records were updated & rowsAffected)
else
response.write ("Records were updated " & rowsAffected )
end if

This works fine and I get confirmation that the records were updated by checking the rowsAffected variable, but when I execute the same sp from Sql Query Analyzer using the same code idea, It did not work. I appreciate someone explain why and what I should do to make it work

Calling the sp from Sql Query Analyzer:
------------------------------------------
Declare @rowsAffected As Int
Execute @rowsAffected = p_update
select @rowsAffected ( This always give me 0 )

The botton line is I want to find a way in which when I call sp from aspx page, I want to be able to confirm that the sp executed successfully. To make my question more complex, what if the sp that is called by aspx contain calls to 3 more sp ( spA, spB, spC ) and I want to make sure that each sp ran successfully so the over all procedure was executed successfully.

Thaks for your advice and help
ehx5

 

[marinero]

set nocount off
p_update

 

[ehx6]

hi, I put setnoncount off inside the store procedure after as

it did not work.
I wonder if I should be putting it in the aspx ?