Getting error when trying to creating a database in sql?

Question

I am trying to create a database in sql using the following

DECLARE @DatabaseName VARCHAR(50) = N'testdb';
EXECUTE ('IF DB_ID(' +@DatabaseName+') IS NULL BEGIN ' + 'CREATE DATABASE [' 
+@DatabaseName+'] END');

but it gives an error:

  Invalid column name 'testdb'

Any ideas on how i can fix this ?

UPDATE: DECLARE @username nvarchar(4000) DECLARE @DatabaseName nvarchar(4000) DECLARE @password nvarchar(4000)

set @username = 'username1'
set @DatabaseName = 'dbtest'
set @password = 'TEST12345'

DECLARE @t nvarchar(4000)
SET @t = 'If not Exists(select loginname from 
master.dbo.syslogins where name = ' + QUOTENAME(@username) + 
     ' BEGIN ' + 
         'CREATE LOGIN '  + QUOTENAME(@username) + ' WITH 
PASSWORD = ' + QUOTENAME(@password, '''') + ', default_database = 
' +
          QUOTENAME(@DatabaseName) + ', DEFAULT_LANGUAGE= 
 [us_english],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF ' + 
         'CREATE LOGIN '  + QUOTENAME(@username) + 'FOR LOGIN ' + 
 QUOTENAME(@username) + 
     ' END'      
 EXEC(@t)



  

Answer 1

It's confusing the variable with a column because you are not enclosing @DatabaseName between '', and you need to escape them with '' or \'

DECLARE @DatabaseName VARCHAR(50) = N'testdb'; EXECUTE ('IF DB_ID('''
+@DatabaseName+''') IS NULL BEGIN ' + 'CREATE DATABASE [' +@DatabaseName+'] END');

Answer 2

The desired SQL statement is:

IF DB_ID('testdb') IS NULL CREATE DATABASE [testdb]

The T-SQL expression to build up this statement is:

N'IF DB_ID(' +
QUOTENAME(@DatabaseName, '''') +
N') IS NULL CREATE DATABASE ' +
QUOTENAME(@DatabaseName)

Notice the use of QUOTENAME to wrap testdb in quotes and in brackets.

• QUOTENAME(@DatabaseName) generates [testdb]
• QUOTENAME(@DatabaseName, '''') generates 'testdb'

QUOTENAME helps prevent potential SQL injection. Always use it in dynamic SQL, even when you are 100% certain your input is sanitized; for two reasons.

1. Nothing is 100% certain. There's nothing wrong with a healthy dose of suspicion, even when it concerns your own code.
2. Your code makes a bad example for other (less experienced) developers.

Unfortunately, the syntax of EXECUTE is rather limited; function calls are not allowed in the string expression. We need a temporary variable to build up the SQL statement.

DECLARE @DatabaseName nvarchar(50) = N'testdb';

DECLARE @creator nvarchar(200) =
    N'IF DB_ID(' +
    QUOTENAME(@DatabaseName, '''') +
    N') IS NULL CREATE DATABASE ' +
    QUOTENAME(@DatabaseName)

EXECUTE (@creator)

Dynamic SQL really hurts readability; it is all too easy to make mistakes. A good way to analyze bugs is to let the generated SQL statement be printed rather than executed.

1. Temporarily replace EXECUTE (@creator) with PRINT @creator
2. Run the SQL script in SSMS; the generated SQL statement will appear in the output pane of SSMS.
3. Copy/paste the output into another query window. Now it's easy to spot the syntax errors; just look for red squiggly lines.
4. Test the generated script, but fix the bugs in the generating script.
5. Repeat the preceding steps until it works.

I strongly recommend to wrap the SQL script above in a stored procedure, as it allows you to properly manage the required permissions, as explained in the article Packaging Permissions in Stored Procedures by Erland Sommarskog.

CREATE PROCEDURE CreateDatabaseIfNotExists @DatabaseName nvarchar(50) AS
    DECLARE @creator nvarchar(200) =
        N'IF DB_ID(' +
        QUOTENAME(@DatabaseName, '''') +
        N') IS NULL CREATE DATABASE ' +
        QUOTENAME(@DatabaseName)
    EXECUTE (@creator)

You can now call the stored procedure from C#. As there is no dynamic SQL here, you do not need to worry about escaping characters in the database name.

using (var con = new SqlConnection(YourConnectionString))
using (var cmd = new SqlCommand("CreateDatabaseIfNotExists", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@DatabaseName", SqlDbType.VarChar).Value = YourDatabaseName;
    con.Open();
    cmd.ExecuteNonQuery();
}