question

I am still not sure why would anyone need something like this, but here is goes. The idea is to pass the delimited list of the database names to the script which generates the create database snippets which can be then executed all at once. To split the list of delimited values in SQL Server 2016 or newer, the string_split may be used. It is not, unfortunately available in earlier versions. The string splitter by @Jeff Moden is the function to use in this case. Alternatively, considering the fact that the list of the databases to create is just that, the single list, and not the column in some table with big number of rows, the split by xml is OK to use here (this is that the script below uses). Here is the script which should work as expected. Please do not run it until it is understood what it does and how it works. To see what it does, just comment out the last line and uncomment the line printing the generated script instead. Here is the script:

-- this will be the stored procedure parameter if needed
declare @list varchar(max) = 'test,test1,test3';

-- these will be the local parameters
declare @sql nvarchar(max) = 'use master';
declare @xml xml  = '<r>' + replace(@list, ',', '</r><r>') + '</r>'
select @sql += ( 
select 
    ';' + char(10) + 'create database ' + x.value('.[1]', 'varchar(128)')
    from @xml.nodes('r') r(x)
    for xml path('')
) + ';'
-- uncomment this line to see what the resulting script looks like
--print @sql

-- do not run until it is understood and agreed that it actually needs to run
exec sp_executesql @statement = @sql;
go

So when the list as in example above is used, the generated script looks like this:

use master;
create database test;
create database test1;
create database test3;

This script may be then executed by calling sp_executesql. It would be best to restate this script as the stored procedure which accepts the list of the databases to create as its single parameter. Also, the script might need to be modified depending on the requirements, i.e. what to do if the database with specified name already exists.

Hope this helps.

Oleg