title: get return value from stored procedure without output parameter
body: <p>I have a vb.net application that inserts records into a db table using a stored procedure that is supposed to return a value. This stored procedure was setup by someone else and initially was linked to a webservice through which my application made the insert and got the return value in the returned xml. I now have access to the db table and not sure how to receive the return value in my vb.net method.</p>
  
  <p>SQl stored procedure snippet;</p>
  
  <pre><code>     @urlname varchar(500),
  @siteid varchar(16),
  @origin varchar(50),
  @queryid varchar(25)
    AS
  SET NOCOUNT ON;
  declare @cnt int
  declare @serverip varchar(16)
  declare @mincnt int
  declare @siteservercnt int
  select @cnt=COUNT(*) from sites
        where urlname=@urlname
  if @cnt = 0
      begin
      insert into sites (urlname,siteid,exported,origin,queryid)
      values(@urlname,@siteid,1,@origin,@queryid)
      select @siteservercnt = COUNT(*) from siteserverip where siteid=@siteid
      if @siteservercnt=0
         begin
              select top 1 @mincnt=COUNT(*),@serverip=serverip from siteserverip
          group by serverip
          order by COUNT(*)
          select top 1 @mincnt=sitecount,
                           @serverip=serverip from serveripcounts
          order by sitecount
          insert into siteserverip values(@siteid,@serverip)
          update serveripcounts set sitecount=sitecount+1
          where serverip=@serverip
          end
      end
  SELECT siteid from sites
  where urlname=@urlname
  return 
  </code></pre>
  
  <p>and my vb.net code to do the insert</p>
  
  <pre><code>        CommandObj.CommandText = "Getsite"
          CommandObj.CommandTimeout = 90
  
          Dim newUrl As String = String.Empty
  
          CommandObj.Parameters.Clear()
  
          Dim m_param As SqlParameter
  
          m_param = CommandObj.Parameters.Add("@urlname", SqlDbType.VarChar, 500)
          m_param.Direction = ParameterDirection.Input
          m_param.Value = name
  
  
          m_param = CommandObj.Parameters.Add("@siteid", SqlDbType.VarChar, 16)
          m_param.Direction = ParameterDirection.Input
          m_param.Value = siteid
  
          m_param = CommandObj.Parameters.Add("@origin", SqlDbType.VarChar, 50)
          m_param.Direction = ParameterDirection.Input
          m_param.Value = method
  
          m_param = CommandObj.Parameters.Add("@queryId", SqlDbType.VarChar, 25)
          m_param.Direction = ParameterDirection.Input
          m_param.Value = forumID
  
  
          Dim recordsAffected As Integer = CommandObj.ExecuteNonQuery
  </code></pre>