Multiple Azure SQL Firewall Rules in One Expression

Question

How do I set MULTIPLE database-level firewall rules in one SQL expression? I have many IP addresses to whitelist over multiple DB's. I'm looking to do something like the following:

EXECUTE sp_set_database_firewall_rule
@name = N'Test1',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x',
@name = N'Test2',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x',
@name = N'Test3',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x',
@name = N'Test4',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x',
@name = N'Test5',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x',,
@name = N'Test6',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x',
@name = N'Test7',
@start_ip_address = 'x.x.x.x', @end_ip_address = 'x.x.x.x'

Running the query above did NOT work. I had to input 1 at a time. Any help is appreciated.

Answer 1

As you stated in your question the syntax your are trying to execute is not supported by T-SQL. What you could do is use dynamic SQL to make it easier-use only one statement:

DECLARE @SQL NVARCHAR(MAX) = '';

;WITH iplists (IPAddrStart, IPAddrEnd, RuleName) AS 
(
    SELECT '100.0.0.0', '100.0.0.1', 'name0'
    UNION ALL
    SELECT '100.0.0.2', '100.0.0.3', 'name1'
    UNION ALL
    SELECT '100.0.0.4', '100.0.0.5', 'name2'
)
SELECT @SQL+= 'EXECUTE sp_set_database_firewall_rule @name = N''' + iplists.RuleName 
+ ''', @start_ip_address = ''' + iplists.IPAddrStart + ''', @end_ip_address = ''' + 
iplists.IPAddrEnd + '''; ' + CHAR(13)
FROM iplists;

--PRINT @SQL;
EXEC (@SQL);