SQL Server adding a variable

Question

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%'

learn.microsoft.com/en-us/sql/t-sql/language-elements/…

Alex K.
– Alex K.

2019-05-22 13:10:33 +00:00
Commented May 22, 2019 at 13:10 — Alex K.
– Alex K., Commented May 22, 2019 at 13:10

Ross Bush · Accepted Answer · 2019-05-22 13:18:03Z

3

I bet you are simply after -->

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

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

edited May 22, 2019 at 13:18

answered May 22, 2019 at 13:10

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HABO Over a year ago

What is the purpose of doubling up the wildcards ('%') by concatenating them to a variable which already contains them?

Ross Bush Over a year ago

Doh!, The op said filename, I assumed that was part of the filename, updated.

Infinity · Accepted Answer · 2019-05-22 13:11:00Z

2

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 + '%'

answered May 22, 2019 at 13:11

Infinity

8984 gold badges16 silver badges42 bronze badges

Comments

Sreenu131 · Accepted Answer · 2019-05-22 14:28:34Z

1

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')

edited May 22, 2019 at 14:28

answered May 22, 2019 at 13:20

Sreenu131

2,5361 gold badge10 silver badges20 bronze badges

1 Comment

HABO Over a year ago

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From.

Collectives™ on Stack Overflow

SQL Server adding a variable

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related