CODEWITHSUNDEEP
c#.netsql-serverstored-proceduressystem.data
user2822362
user2822362

Reputation: 127

Select Query and Update Query in same StoredProcedure

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.

Upvotes: 1

Views: 1569

Answers (4)

eBlack
eBlack

Reputation: 7

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

Upvotes: 1

ozzijb
ozzijb

Reputation: 694

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

Refer to this post for examples.

Upvotes: 0

Raki
Raki

Reputation: 535

This works fine

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

Upvotes: 1

Nizam
Nizam

Reputation: 4699

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;

Upvotes: 1

Related Questions