Reputation: 7397

SQL Server adding a variable

How can I add a variable in SQL Server to replace %MV_AUG2019.json% so that I only have to change it in one spot for all of those tables?

delete from [addresses] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [disabilities] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [MD] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [messages] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [MH] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [MV] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [permits] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [PP] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [registrationOwners] where [uniqueFilename] like '%MV_AUG2019.json%'
delete from [stops] where [uniqueFilename] like '%MV_AUG2019.json%'

Upvotes: 0

Answers (3)

Sreenu131

Reputation: 2516

you can generate tables in dynamic

DECLARE @FileName NVARCHAR(500) = 'MV_AUG2019.json'

SELECT 'DELETE FROM '+QUOTENAME(TABLE_NAME) +' WHERE '+ QUOTENAME(COLUMN_NAME)+ ' LIKE '+'''%'+ @FileName+'%'''
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME ='uniqueFilename' AND DATA_TYPE IN ('char','nvarchar','varchar')

Upvotes: 1

Ross Bush

Reputation: 15155

I bet you are simply after -->

DECLARE @FileName NVARCHAR(500) = 'MV_AUG2019.json'

delete from [addresses] where [uniqueFilename] like '%'+@FileName+'%'

Upvotes: 3

Infinity

Reputation: 898

You have to declare a variable in the following:

DECLARE @var NVARCHAR(1000) = 'name.json' -- provide file name here

delete from [addresses] where [uniqueFilename] like '%' + @var + '%'

Upvotes: 2

SQL Server adding a variable

Answers (3)

Related Questions