CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Tom van den Bogaart
Tom van den Bogaart

Reputation: 143

Saving A Variable with SELECT Query

I've written a stored procedure where I delete files from my database.

But I'm getting an error when I execute my query that I have more then one value which is correct.

But I want to delete all the values in my table. Without getting the error

CREATE PROCEDURE [dbo].[DeleteArticle]
@Article_ID int
AS

DECLARE @IDS int
SET @IDS = (SELECT [File_ID] FROM Article_Files WHERE Article_ID = @Article_ID);

DELETE FROM Article_Files WHERE Article_ID = @Article_ID;
DELETE FROM Files WHERE [FILE_ID] IN (@IDS);
DELETE FROM Article WHERE Article_ID = @Article_ID;
RETURN 0

The error is in de 5-6 six line of code where I SET the @IDS.

Upvotes: 1

Views: 740

Answers (1)

Roman Marusyk
Roman Marusyk

Reputation: 24609

You can't store result of SELECT that returns many items into INT variable

DECLARE @IDS int
SET @IDS = (SELECT [File_ID] FROM Article_Files WHERE Article_ID = @Article_ID);

In this case, the @IDS value will be the last File_ID of SELECT

You can try this

DECLARE @Ids TABLE (Id INT)
INSERT INTO @Ids
SELECT [File_ID] FROM Article_Files WHERE Article_ID = @Article_ID

DELETE FROM Article_Files WHERE Article_ID = @Article_ID;
DELETE FROM Files WHERE [FILE_ID] IN (SELECT Id FROM @Ids);
DELETE FROM Article WHERE Article_ID = @Article_ID;
RETURN 0

Upvotes: 1

Related Questions