execute INSERT SQL and get return value

Sumber : http://planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=1893&lngWId=10&txtForceRefresh=5212012541567681

execute INSERT statement and get the return value (either success or not). This method also useful for return rowcount (@@rowcount). This will encourage many developers doing the validation at Stored Procedure level
 

//**************************************
// Name: execute INSERT SQL and get return value
// Description:execute INSERT statement and get the return value (either success or not). This method also useful for return rowcount (@@rowcount). This will encourage many developers doing the validation at Stored Procedure level
// By: Walter Wong Woon Sui
//
//This code is copyrighted and has// limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1893&lngWId=10//for details.//**************************************

Stored Procedure
 CREATE PROCEDURE SetGroupEntity
  @Name nvarchar(30) ,
  @Description nvarchar(50) =NULL,
  @flag int OUTPUT
 AS
  IF EXISTS(SELECT * FROM GroupEntity WHERE NAME=@NAME)
  BEGIN
   SET @flag=1
   return @flag
  END
  ELSE
  BEGIN
   INSERT INTO GroupEntity([Name], [Description], LastUpdatedDate)
   VALUES (@Name, @Description, CURRENT_TIMESTAMP)
   Set @flag=0
   return @flag
  END
VB Functions
con = New SqlConnection(ConfigurationSettings.AppSettings("conBank"))
con.Open()
cmd = New SqlCommand
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SetGroupEntity"
With cmd.Parameters
.Add("@Name", iName)
.Add("@Description", iDescription)
.Add("@flag", SqlDbType.Int) 'parameter return from stored procedure
End With
cmd.Parameters("@Name").Direction = ParameterDirection.Input
cmd.Parameters("@Description").Direction = ParameterDirection.Input
cmd.Parameters("@flag").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
iFlag = cmd.Parameters("@flag").Value 'get return value
cmd.Dispose()
con.Close()
con.Dispose()