Select Query and Update Query in same StoredProcedure

Question

I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure. For e.g.

Create Procedure [dbo].[myProcedure]
As
BEGIN
BEGIN TRAN T1
SET NOCOUNT ON
SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1;
UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id;
SELECT * FROM #TempTable;
DROP Table #TempTable;
COMMIT TRAN T1
END

The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.

private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection)
        {
            List<ProcessData> Data = new List<ProcessData>();
            SqlCommand Command = new SqlCommand(StoredProcedure, Connection);
            Command.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                Command.CommandTimeout = CONNECTION_TIMEOUT;
                using (SqlDataReader Reader = Command.ExecuteReader())
                {
                    while (Reader.Read())
                    {
                        Data.Add(new ProcessData()
                        {
                              Id = Reader["Id"];
                              ...
                           });
                    }
                }
            }
            catch (Exception ex)
            {}
        }

The problem is I'm getting the required rows in C# but the update query in stored procedure is not working. Can anyone give some suggestions where I'm going wrong.

is **"From myTable Inner Join myTable.id = #TempTable.id;"** your real code — Mohsen, May 04 '15 at 18:47
You are ignoring the exception in `catch (Exception ex){}`. If `COMMIT TRAN T1` fails for any reason, you'll get the rows, but updates will be rolled back. I suspect this is what's happening. — Diego, May 04 '15 at 20:31
I'm handling the exception in real code, but I'm not getting any exception. In code I'm getting the selected rows. Update query not updating the value. If I debug stored procedure in SQL update query is working fine — user2822362, May 05 '15 at 05:30

Nizam · Answer 1 · 2015-05-04T18:56:52.413

The following line of procedure

UPDATE myTable 
SET [Status] = 'Locked' 
From myTable 
Inner Join myTable.id = #TempTable.id;

should be

UPDATE myTable 
SET [Status] = 'Locked' 
From myTable 
Inner Join #TempTable 
   on myTable.id = #TempTable.id;

And COMMINT TRAN T1 should be replaced by COMMIT TRAN T1

And finally, you should not use SELECT TOP without ORDER BY

SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1;

score 1 · Answer 2 · answered May 04 '15 at 19:08

1

This works fine

UPDATE
A
SET
foo = B.bar
FROM
TableA A
JOIN
TableB B ON A.col1 = B.colx
WHERE
...

answered May 04 '15 at 19:08

Raki

535
4
13

eBlack · Answer 3 · 2015-05-08T13:56:50.540

1

UPDATE myTable 
SET [Status] = 'Locked'  
From myTable  
WHERE id in (select TOP 5 * FROM myTable WHERE enabled=1)

edited May 08 '15 at 13:56

answered May 04 '15 at 21:04

eBlack

7
5

score 0 · Answer 4 · answered May 06 '15 at 17:41

0

You can use the OUTPUT clause to do this in one statement without creating a temp table.

Refer to this post for examples.

answered May 06 '15 at 17:41

ozzijb

674
5
10
17

Select Query and Update Query in same StoredProcedure

4 Answers4