How do I delete a selected column in SQL Server, inside a stored procedure
I am using SQL Server 2017 Express to implement a high-performance queue (FIFO push & pop) - by "high-performance", I mean every millisecond counts for my application.
I ended up implementing the queue's
pop function like this:
BEGIN TRY DECLARE @QueueID [int] = null; --get the id of the top 1 item SELECT TOP 1 @QueueID = [QueueID] FROM [dbo].[ReceiverQueue] WITH (NOLOCK); --pop that item SELECT [QueueDateTime], [Packet], [Status] FROM [dbo].[ReceiverQueue] WHERE [QueueID] = @QueueID OPTION (FAST 1); --delete that item from the queue DELETE FROM [dbo].[ReceiverQueue] WHERE [QueueID] = @QueueID; --return the popped columns RETURN; END TRY BEGIN CATCH --return exception, if pop fails EXECUTE [dbo].[sp.GetLastError] END CATCH;
Some of the solutions that I came across in StackOverflow uses temporary tables to persist the result-set within a stored procedure for further use (in my case for the
DELETE statement that follows the
SELECT); and I created a short variant of that as shown above...
Nevertheless, I was wondering whether there exists a variant like this?
SELECT TOP 1 @QueueID = [QueueID], [QueueDateTime], [Packet], [Status] FROM [dbo].[ReceiverQueue] WITH (NOLOCK);
DELETE FROM [dbo].[ReceiverQueue] WHERE [QueueID] = SELECTED.QueueID;
Tried similar, but in vain.
Thanks in advance.
QueueID is an
If you have SQL Server 2005 or higher (and if you don't, you really should upgrade), you can POP your data using the OUTPUT clause of the DELETE statement, like this:
DELETE FROM [dbo].[ReceiverQueue] OUTPUT deleted.[QueueDateTime], deleted.[Packet], deleted.[Status] WHERE [QueueID] = ( SELECT TOP 1 [QueueID] FROM [dbo].[ReceiverQueue] WITH (NOLOCK) )
SELECT against stored procedure SQL Server, the following: declare @t table ( -- columns that are returned here ); insert into @t(<column list here>) exec('storedp_Value'); Expand Stored Procedures, right-click the procedure to remove, and then click Delete. To view objects that depend on the procedure, click Show Dependencies. Confirm the correct procedure is selected, and then click OK. Remove references to the procedure from any dependent objects and scripts.
If perfomance is a problem and you need ORDER BY to ensure deleted row is really the first one logically you may try deleting from CTE
with c as ( SELECT top(1) * FROM [dbo].[ReceiverQueue] ORDER BY [QueueID] ) delete from c output deleted.[QueueDateTime], deleted.[Packet], deleted.[Status]
Select columns from result set of stored procedure, How can get column value from stored procedure in SQL? The UPDATE statement is used to edit and update the values of an existing record. The DELETE statement is used to delete records from a database table. The following SQL stored procedure is used insert, update, delete, and select rows from a table, depending on the statement type parameter. ALTER PROCEDURE Masterinsertupdatedelete (@id INTEGER,
Thanks to all of you; really appreciate the fast useful responses.
Combined your recommendations and coined a variant:
--pop top 1 row DELETE TOP (1) FROM [dbo].[ReceiverQueue] OUTPUT DELETED.*; --return the popped columns RETURN;
Worked like charm! :)
PS. The sort order could still be a mess as Serg pointed out; need to explore this discussion in that regard... But for now, the above revision looks suffice.
Select, Insert, Update, Delete Using Stored Procedure in SQL Server , is to use the INSERT INTO EXEC syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need. Delete SQL Server Stored Procedure. Hopefully the reasons above are enough to convince you that checking the count is a good way to help avoid these issues, so let's take a look at a stored procedure I put together that will validate the number of records we remove when we are performing a delete.
Delete a Stored Procedure, How do you insert insert update delete in one stored procedure? SELECT Stored Procedure in SQL Server Example. In this SQL Server example, we will show you how to use the SELECT Statement inside the Stored procedure. I suggest you refer Introduction to Stored Procedures in SQL Server article to know the basics.
SQL Server Stored Procedure to Safely Delete Data, Select, Insert, Update, Delete Using Stored Procedure in SQL Server 2008. Rohatash Then execute a SELECT SQL query to display all records of the table. INSERT You will see a new row added to the database table. “How to select columns from Stored Procedure Resultset?” Though Stored Procedure has been introduced many years ago, the question about retrieving columns from Stored Procedure is still very popular with beginners. Let us see the solution in quick steps. First we will create a sample stored procedure.
Overview of the SQL DELETE Column from an existing table operation, Confirm the correct procedure is selected, and then click OK. Remove references to the procedure from any dependent objects and scripts. Using Here Mudassar Ahmed Khan has explained with an example, how to perform Select, Insert, Update and Delete operations using a single Stored Procedure in SQL Server. Performing Select, Insert, Update and Delete operations using a single Stored Procedure is supported in SQL Server versions i.e. 2005, 2008, 2008R2, 2012, 2014 or higher. TAGs: SQL Server, Stored Procedures