CODEWITHSUNDEEP
sqlsql-servert-sql
Kat Isenberg
Kat Isenberg

Reputation: 99

Declare variables and call

I have a bunch of delete queries that I have to copy in values for each. For instance; 

delete from source where id in (3300,3301, 2872)
delete from performance where srcid in (3300,3301, 2872)
delete from title where id in (3300,3301, 2872)
delete from name where srcid in (3300,3301, 2872)

I want to declare the values (3300,3301, 2872) because they change each time. I have to copy for each query. I understand how to declare when it is one number with a declare and set @source = but am confused as to how to get the in

I have tried many variations from the net. This is the latest variation. It doesn't give an error, but it does not find the items in the list. I couldn't figure out the sp or function creations from the net.

DECLARE @sourcelist VARCHAR = '2380,2379'
--SET @sourcelist = '2380' + ',' + '2379'

select * from dbo.Source where id in (@sourcelist)



DECLARE @sourcelist VARCHAR = '2380,2379'
--SET @sourcelist = '2380' + ',' + '2379'

select * from dbo.Source where id in (@sourcelist)

--------------------------------

Here is a variation of the sp

DECLARE @sourcelist VARCHAR(500)
DECLARE @delete1 NVARCHAR(4000)
SET @delete1 = 'delete from [EID].[FileLoadLog] where srcid in (CAST(@sourcelist AS varchar(500)))'

BEGIN
EXEC sp_executesql @delete1 
END

these are each one row. I expect 2 rows to delete.

Upvotes: 0

Views: 58

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

There are many ways to do this. If you want to use dynamic SQL, here is an option where we replace a "token" with your string of ids

Example

Declare @IDs varchar(max) = '3300,3301,2872'

Declare @SQL varchar(max) = '
delete from source where id in ([getList]);
delete from performance where srcid in ([getList]);
delete from title where id in ([getList]t);
delete from name where srcid in ([getList]);
'
Set @SQL = replace(@SQL,'[getList]',@IDs)
Exec(@SQL)

Upvotes: 1

Serg
Serg

Reputation: 22811

You can do it with a table variable as well

declare @items table(
  id int
);

insert @items(id)
values (3300),(3301), (2872);

delete from source where id in (select id from @items);
delete from performance where srcid in (select id from @items);
delete from title where id in (select id from @items);
delete from name where srcid in (select id from @items);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

It looks like you are using SQL Server, so I'll use that syntax.

Just store the values in a temporary table and use that:

select v.*
into #temp_ids
from (values (3300), (3301), (2872)) v(id);

delete from source where id in (select id from #temp_ids);
delete from performance where srcid in (select id from #temp_ids);
delete from title where id in (select id from #temp_ids);
delete from name where srcid in (select id from #temp_ids);

Upvotes: 1

Related Questions