Create SQL Server database with parameterised inputs using nodejs and mssql module

Question

I'm trying to build a nodejs CLI to interact with SQL Server 2017 using the mssql module. The first element of this is the dynamic creation of a database as part of a larger async function. The user will provide the name and size of the database and the CLI will create it in a default location on the machine.

Example with hardcoded values:

let newDB = await pool.request()
    .query(`CREATE DATABASE [testDB] ON PRIMARY
            (NAME = N'testDB',
                    FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\DATA\\testDB.mdf',
                    SIZE = 100MB,
                    FILEGROWTH = 100MB)
                LOG ON
            (NAME = N'testDB_log',
                FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\DATA\\testDB_log.ldf',
                SIZE = 100MB,
                FILEGROWTH = 100MB);`)

This worked fine and created a database called testDB in the correct location at the correct size.

Example with single parameterised input (the database name):

let newDB = await pool.request()
    .input('dbName', sql.VarChar, 'testDB')
    .query(`CREATE DATABASE [@dbName] ON PRIMARY
            (NAME = N'testDB',
                    FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\DATA\\testDB.mdf',
                    SIZE = 100MB,
                    FILEGROWTH = 100MB)
                LOG ON
            (NAME = N'testDB_log',
                FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\DATA\\testDB_log.ldf',
                SIZE = 100MB,
                FILEGROWTH = 100MB);`)

This creates a database, but it's called @testDB - essentially ignoring the inputs value and reading it as a value itself.

Example with all inputs:

let newDB = await pool.request()
    .input('dbName', sql.VarChar, Name_InputVariable) //Name of database e.g. TestDB
    .input('dbSize', sql.Int, Size_InputVariable) //Size of database e.g. 100
    .input('dbLocation', sql.VarChar, Location_InputVariable) //Location of database e.g. location on c drive    
    .query(`CREATE DATABASE [@dbName] ON PRIMARY
            (NAME = N'@dbName',
                    FILENAME = N'@dbLocation\\@dbName.mdf',
                    SIZE = @dbSize MB,
                    FILEGROWTH = 100MB)
                LOG ON
            (NAME = N'@dbName_log',
                FILENAME = N'@dbLocation\\@dbName_log.ldf',
                SIZE = 100MB,
                FILEGROWTH = 100MB);`)

This doesn't work and comes back with the following error:

RequestError: Incorrect syntax near '@var2'.
code: 'EREQUEST',
originalError: [Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '@var2'.] {
sqlstate: '42000',
code: 102
},
name: 'RequestError',
number: 102,
state: '42000'
}

I fully expected there to be errors when I put all the variables in, but it's very hard to start debugging any code when even at a basic level, with a single input, it isn't doing as I would have expected. Most examples I've found just show single inputs used in where clauses which works fine, but isn't what I'm trying to achieve.

For reference I have managed to get simple where clause queries to work, but am I missing something with these kind of inputs that they cant be used in create database queries or something similar...? If this is the case what is the correct way to dynamically create a SQL Server database using node js?

Any help or insight into this would be greatly appreciated.

David Browne - Microsoft · Accepted Answer · 2020-11-09 18:55:55Z

1

am I missing something with these kind of inputs that they cant be used in create database queries or something similar...?

Yes.

You'll have to use string concatenation/interpolation as SQL Server does not support parameterization of Data Definition Language (DDL) statements.

answered Nov 9, 2020 at 18:55

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Create SQL Server database with parameterised inputs using nodejs and mssql module

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related