Where to put single quotes?

0 votes

1 answer

92 views

sql-server t-sql sql-server-2014 dynamic-sql quoted-identifier

                          I am trying to execute the below statement which creates a database called service2019:

    DECLARE @dbname VARCHAR(50);
    SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
    
    
    EXEC (
    'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY 
    ( NAME =' + '' + @dbname +'' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + '''' + '''.mdf'''  + ', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
     LOG ON 
    ( NAME =' + '' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + ''''+'''.ldf''' + ', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'
    )

I get the correct database name and logical names. The problem is I get wrong physical names:

'service2019'.mdf

'service2019'.ldf

Could someone explain to me how these quotes actually work? There are some parts where it is clear but sometimes it becomes a challenge to figure out where to put those quotes. 
                        

Asked by Rauf Asadov (1313 rep)

Dec 16, 2019, 02:07 PM
Last activity: Dec 16, 2019, 04:27 PM

Where to put single quotes?

Related Questions