assign variable to a variable of stored procedure

[xxxtian]

I have this code in design's code behind

int id;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_addvisitor";
cmd.Connection = con;
cmd.Parameters.Add("visitorname", SqlDbType.NVarChar).Value = textBox1.Text;
SqlParameter bookidparam = new SqlParameter("@visitorid", SqlDbType.Int);
bookidparam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(bookidparam);
id = Convert.ToInt32(bookidparam.Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

       for (int i = 0; i < checkedListBox1.Items.Count; i++)
            {
                
                if (checkedListBox1.GetItemChecked(i) == true)
                {
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "usp_addvisitoreventtype";
                    cmd.Parameters.Add("@eventname", SqlDbType.BigInt).Value = checkedlistbox1.Items[i];
                    con.Open();
                    cmd1.ExecuteNonQuery();
                    con.Close();
                }
            }

My stored procedure is

CREATE PROCEDURE usp_addvisitoreventtype

@visitorid bigint,
@eventtypeid bigint,
@eventname nvarchar(50)


AS

INSERT INTO tblVisitorEvent (visitorID, eventTypeID) select tblVisitor.visitorID, tblEventType.eventTypeID from tblVisitor CROSS JOIN tblEventType where tblEventType.eventTypeID = @eventname AND tblVisitor.visitorID = @visitorid or local variable???

I don't know how to pass the output identity variable that is set to

id = Convert.ToInt32(bookidparam.Value);

to my where call clause tblVisitor.visitorID = ???.. Please help. I can only do this by only using a inline sql statement in design's code behind.

"insert into BATest select BooksTest.Book_ID, Author.Author_ID from BooksTest CROSS JOIN Author where Author.Author_Name = @check AND BooksTest.Book_ID = '"+id+"'";

where the output identity variable is set to the where clause of bookstest.book_id.

[kvprajapati]

OUTPUT parameter must be accessed after the execution of SP is finished.

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

id = Convert.ToInt32(bookidparam.Value);

You can also call a SP from another SP.

SP - GetSquare

CREATE PROCEDURE GetSquare
 @no int ,
 @square int OUTPUT
AS
 set @square=@no*@no
RETURN

SP - Call "GetSquare"

CREATE PROCEDURE CallGetSquare
AS
 declare @no int
 declare @square int

 set @no=2
	
 execute GetSquare @no,  @square OUT 
 /* debug */ 
 select @no, @square
RETURN