Having a stored procedure affect only the currently focused row

Question

I’m using Visual Studio with DevExpress Winforms in conjunction with Microsoft SQL Server Manager, and I’m currently trying to write a stored procedure that will activate on a button click, copying the focused row to a different table.

I’ve looked into it and haven’t been able to find a solution that does what I need it to. I would prefer not to use checkboxes if possible, but am open to it.

My current code within the stored procedure is:

CREATE PROCEDURE [dbo].[procedurename]
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM tbl_b)
        INSERT INTO tbl_b (column names here)
            SELECT DISTINCT (column names here)
            FROM tbl_a
END

I’ve been able to successfully call this procedure when testing the program with the button, and it functions mostly as intended, moving the data from one table to the other with no duplicates, however it moves the entire table when I only want to move a focused row.

I’m using gridView for this, and I’ve tried solutions that use DataGridView that don’t seem to work.

This is a desktop based program.

Answer 1

You need a parameter in your stored procedure that holds an identification of the row you want to copy

CREATE PROCEDURE [dbo].[procedurename] (@ID int)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM tbl_b)
      INSERT INTO tbl_b (column names here)
        SELECT DISTINCT (column names here)
        FROM tbl_a
        where tbl_A.PrimaryKeyColumn = @ID
END

In the Click event of your button you could then do something like this

var id = myGridView.GetRowCellValue(myGridView.FocusedRowHandle, "YourPrimaryKeyName");

and now you can use this id variable to pass to your stored procedure

However, you do have another problem in your stored procedure

    IF NOT EXISTS (SELECT * FROM tbl_b)

This check will never allow you to do your insert once there is 1 or more rows in that table.

I think you need something like

    IF NOT EXISTS (SELECT 1 FROM tbl_b where tbl_b.PrimaryKey = @ID)