Post reply

single quote in sql statement

  • December 14, 2012 at 6:07 pm

    #286218

    I would like to set a variable that is a sql statement, but the syntax about the quotes is so confusing to me, I cannot make it right:

    What I have is this:

    SET @sqlcmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''H:\Preupgrade\ Backups\'+@dbname+'_'+@filedate +' WITH NOFORMAT, NOINIT,' +

    ' NAME = '''+@dbname+@filedate+''', SKIP, REWIND, NOUNLOAD, STATS = 10'

    then EXEC sp_executesql @sqlcmd

    How can I use single quotes in statement like above?

    Thanks

  • December 14, 2012 at 9:05 pm

    #1568872

    This is what you are trying to accomplish:

    declare @dbname varchar(128) = 'TestDB',

    @filedate varchar(64) = '20121214210000',

    @sqlcmd varchar(max);

    SET @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''H:\Preupgrade\ Backups\' +

    @dbname + '_' + @filedate + '''' + ' WITH NOFORMAT, NOINIT, ' +

    'NAME = ' + '''' + @dbname + @filedate + '''' + ', SKIP, REWIND, NOUNLOAD, STATS = 10';

    print @sqlcmd;

  • December 14, 2012 at 9:52 pm

    #1568878

    If the no. of quotes are still confusing for you in the solution provided by Lynn......then you can also use the CHAR() function in sql server to apply quotes by using the ASCII value of a single quote(39) as shown below :

    declare @dbname varchar(128) = 'TestDB',

    @filedate varchar(64) = '20121214210000',

    @sqlcmd varchar(max);

    SET @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ' + CHAR(39) + 'H:\Preupgrade\

    Backups\' + @dbname + '_' + @filedate + CHAR(39) + ' WITH NOFORMAT, NOINIT, ' +

    'NAME = ' + CHAR(39) + @dbname + @filedate + CHAR(39) + ', SKIP, REWIND, NOUNLOAD,

    STATS = 10';

    print @sqlcmd;

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • December 15, 2012 at 12:04 pm

    #1568892

    Thank you both for this, this makes much clearer to understand!

  • December 15, 2012 at 1:48 pm

    #1568893

    Since this is a backup you are trying to run, here is another option:

    declare @dbname varchar(128),

    @filedate varchar(64);

    declare @diskfile varchar(256) = 'H:\Preupgrade\Backups\' + @dbname + '_' + @filedate;

    declare @filename varchar(256) = @dbname + @filedate;

    declare @SQLCmd varchar(max) =

    'BACKUP DATABASE @dbname

    TO DISK = @diskfile

    WITH NOFORMAT,

    NAME = @filename,

    SKIP,

    REWIND,

    NOUNLOAD,

    STATS = 10;';

    declare @params @varchar(max) = '@dbname varchar(128), @diskfile varchar(64), @filename varchar(256)';

    exec sp_executesql

    @SQLCmd,

    @params,

    @dbname = @dbname,

    @diskfile = @diskfile,

    @filename = @filename;

  • December 17, 2012 at 12:56 pm

    #1569255

    Thanks, that is helpful!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply