handle return values from stored procedure

[AngelicOne]

While using an insert query for a certain table, can I used a select statement to know whether there's an already existing value in the stored procedure using a return value where insert occurs? Or do I need to separate a procedure? I don't know much of return values.

[kvprajapati]

Yes! you can use select statement.

Stored procedure to insert a record (Table dept (deptno, deptname))

CREATE PROCEDURE add_dept
	 @deptno int,
	 @deptname varchar(30)
AS
declare @count int

select @count=count(*) from dept
             where deptno=@deptno  

if @count=0
   begin
     insert into dept (deptno,deptname)
          values (@deptno,@deptname)
   end           
RETURN

C# code to execute "add_dept" SP.

SqlConnection cn = new SqlConnection(@"connection_string_here");
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = cn;
            cmd.CommandText = "add_dept";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@deptno", 10);
            cmd.Parameters.AddWithValue("@deptname", "Accounting");

            cn.Open();
            int return_value = cmd.ExecuteNonQuery();
            cn.Close();

            if (return_value == 1)
            {
                MessageBox.Show("Added");
            }
            else
            {
                MessageBox.Show("Duplicate");
            }

[AngelicOne]

You didn't put a sqlparameter direction?

[kvprajapati]

You didn't put a sqlparameter direction?

The default is Input.

[AngelicOne]

The default is Input.

Yes, and I just noticed that you didn't use a return value in your stored procedure. Do you prefer to do it in input rather than return value parameter?

[kvprajapati]

Take a look at this thread (OUT parameter) - http://www.daniweb.com/forums/thread347508.html

[AngelicOne]

Take a look at this thread (OUT parameter) - http://www.daniweb.com/forums/thread347508.html

Ok. Thanks for all the info but I'm currently having an error handling a return value parameter. Here is my stored procedure.

ALTER PROCEDURE [dbo].[usp_addeventtype]

@eventname nvarchar(50),
@startdatetime smalldatetime,
@enddatetime smalldatetime,
@location nvarchar(50),
@contactperson nvarchar(50)

AS

declare @count int

select @count=count(*) FROM tblEventType WHERE eventName = @eventname
if @count=0
begin
INSERT INTO tblEventType
VALUES
(@eventname, @startdatetime, @enddatetime, @location, @contactperson)
return 1
end
else
begin
return -1
end

This procedure returns the integer 1 if there's no duplicate of an event name in tblEventType.

This is my insert button event click method in VS

if (eventname.Text == string.Empty)
            {
                MessageBox.Show("Please enter an event name.", "message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                eventname.Focus();
            }
            else
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_addeventtype";
                cmd.Connection = con;

                cmd.Parameters.AddWithValue("@eventname", eventname.Text);
                cmd.Parameters.AddWithValue("@startdatetime", startdatetime.Text);
                cmd.Parameters.AddWithValue("@enddatetime", enddatetime.Text);
                cmd.Parameters.AddWithValue("@location", location.Text);
                cmd.Parameters.AddWithValue("@contactperson", contact.Text);

                SqlParameter eventparam = cmd.Parameters.AddWithValue("@count", SqlDbType.Int);
                eventparam.Direction = ParameterDirection.ReturnValue;

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

                int checkevent = Convert.ToInt32(eventparam.Value);

                if (checkevent == 1)
                {
                    MessageBox.Show("Event type successfully added.", "message", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    this.DialogResult = DialogResult.OK;
                    this.Close();
                }
                else if (checkevent == -1)
                {
                    MessageBox.Show("Event name already exist. Please try another.", "message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

It does detect if there's a duplicate value, and shows an alert message. There's a problem though. I don't know if i misplaced the codes, but whenever I click the button again after it finds a duplicate value with an alert message, it shows an error "stored procedure has too many arguements specified". Why is that? It works at first, but if I press the button again using the same value, it gives an error.