Error converting datatype varchar to bigint- SQL Server-CodePudding

I am trying to run a delete query in stored procedure but I keep getting the following error:

Error converting data type varchar to bigint.

request_id column of workflow_status is of type bigint .

Below is what I am trying to do in my stored procedure:

DECLARE @wp_ids_to_delete varchar(8000)
    
SET @wp_ids_to_delete = 'SELECT id FROM [dbo].[wp_req] WHERE creation_date < DATEADD(YEAR, -1, GETDATE())';

DELETE FROM [dbo].[workflow_status] 
WHERE request_id IN (   @wp_ids_to_delete   );

The SELECT query works fine:

SET @SqlStatement_workflow_status = 'SELECT * FROM [dbo].[workflow_status] WHERE request_id IN ('   @wp_ids_to_delete   ')';

CodePudding user response：

You don't need to build a list and pass it to another statement (even if you were building the list correctly and IN ('id1,id2') was valid):

DELETE ws
  FROM dbo.workflow_status AS ws
  WHERE EXISTS
  (
    SELECT 1 FROM dbo.wp_req AS wr
      WHERE wr.id = ws.request_id
      AND wr.creation_date < DATEADD(YEAR, -1, GETDATE())
  );

CodePudding user response：

Your example SELECT you believe to be working fine, is actually not a SELECT statement at all. It's a SET statement that is working fine because it is only setting a variable to a string value, that just so happens to be written in the form of a SELECT statement.

Similarly, your delete statement fails for the same reason. That is because you're asking it to delete rows where the request_id is in a string value, that the variable was set to. Below is what the overall effect of your statement, as you have it written, would be when it's executed. As you can see, the value of 'SELECT id FROM [dbo].[wp_req] WHERE creation_date < DATEADD(YEAR, -1, GETDATE())' cannot be converted to an INT.

DELETE FROM [dbo].[workflow_status] 
WHERE request_id IN (   'SELECT id FROM [dbo].[wp_req] WHERE creation_date < DATEADD(YEAR, -1, GETDATE())'   );

Try this for you delete statement instead.

DELETE FROM [dbo].[workflow_status]
WHERE  request_id IN (
    SELECT id
    FROM [dbo].[wp_req]
    WHERE creation_date < DATEADD(YEAR, -1, GETDATE())
)

Also, take into consideration how many rows will be deleted by this statement, and any triggers that may fire when you run it. It could cause a lot of blocking while it runs and overhead in your transaction log. For example, if you expect it to delete a few hundred thousand rows, or millions of rows, you may want to consider a batched approach where you delete around 10,000 rows at a time.

CodePudding user response：

Your SELECT doesn't go "KABOOM"; that's not the same as it working the way you think it does.

If you were to do this:

SET @SqlStatement_workflow_status =  'Select * from [dbo].[workflow_status] where request_id IN ('  @wp_ids_to_delete   ')';
SELECT @SqlStatement;

You'd see that it returns:

Select * from [dbo].[workflow_status] where request_id IN (SELECT id FROM [dbo].[wp_req] WHERE creation_date < DATEADD(YEAR, -1, GETDATE()))

Without it actually running that query.

Now the other two answers of putting the query represented by @wp_ids_to_delete in directly as a subquery will work, but I believe they can be improved upon, in terms of future maintainability, by using a JOIN:

DELETE s 
FROM [dbo].[workflow_status] s
INNER JOIN [dbo].[wp_req] r 
ON r.id = s.request_id
AND r.creation_date < DATEADD(YEAR, -1, GETDATE());

Note, as Aaron points out in the comments, all 3 of the currently suggested answers work out into identical execution plans. There isn't a quicker or more efficient option of the 3; it's down to preferred style of query.

If you do want store the list of ID's as a comma seperated string for later use, then you could do:

SET @wp_ids_to_delete = SELECT STRING_AGG(id) FROM [dbo].[wp_req] WHERE creation_date < DATEADD(YEAR, -1, GETDATE());

And your SELECT might become:

SET @SqlStatement_workflow_status =  'Select * from [dbo].[workflow_status] where request_id IN ('  @wp_ids_to_delete   ');';
EXEC @SqlStatement_workflow_status;

And your DELETE might become:

SET @delete_Statement = 'DELETE FROM [dbo].[workflow_status] 
WHERE request_id IN ( '  @wp_ids_to_delete  ' );';
EXEC @delete_Statement;

But, this may well be slower than other options.