title: Adding Single Quotes Around A Scalar Variable
body: <p>I have a stored procedure in which I am trying to execute a @cmd variable which is a BACKUP database command. When I run the stored procedure (which is in a job through the reporting agent) I get an error with the 'C:' in my path, I believe because the path is not encased in quotes. How can I encase the @Filename variable I have to have quotes around it so the database backup command can work?</p>
  
  <p>Here is the code for the stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE pAgentBackup
  AS
  --1. Declare Variables.
  DECLARE @MaxID INT
  DECLARE @MinID INT
  DECLARE @CurrentID INT
  DECLARE @Path VARCHAR(100)
  DECLARE @DBName VARCHAR(50)
  DECLARE @Type CHAR(1)
  DECLARE @cmd VARCHAR(500)
  DECLARE @Recovery VARCHAR(300)
  DECLARE @Filename VARCHAR(100)
  
  SELECT @CurrentID = 0, @MaxID = MAX(BKID) FROM DBBackups
  
  --2. Loop through the columns in the table and execute the backups.
  WHILE @CurrentID &lt; @MaxID
      BEGIN
          SELECT @CurrentID = MIN(BKID) FROM DBBackups WHERE BKID &gt; @CurrentID
  
          SELECT @DBName = DbName FROM DBBackups WHERE BKID = @CurrentID
          SELECT @Path = Path FROM DBBackups WHERE BKID = @CurrentID
          SELECT @Type = Type FROM DBBackups WHERE BKID = @CurrentID
  
          IF @Type = 'F'
              BEGIN
                  SELECT @Recovery = CAST(DATABASEPROPERTYEX(name, 'Recovery') AS VARCHAR(25))
                  FROM master.dbo.sysdatabases WHERE name = @DBName
  
                  --Set recovery to full if it is not already turned on.
                  IF @Recovery &lt;&gt; 'FULL'
                      BEGIN
                          SELECT @cmd = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL'
                          EXEC (@cmd)
                      END
  
                  --BEGIN Backup
                  SET @Filename = @Path + @DBName + 'Full' +  CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
                  SET @cmd = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ' + @Filename
                  EXEC (@cmd)
  
                  SET @Filename = @Path + '\Log' + @DBName + 'Log' +  CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
                  SET @cmd = 'BACKUP LOG ' + @DBName + ' TO DISK = ' + @Filename
                  EXEC (@cmd)
              END
  
          IF @Type = 'L'
              BEGIN
                  SELECT @Recovery = CAST(DATABASEPROPERTYEX(name, 'Recovery') AS VARCHAR(25))
                  FROM master.dbo.sysdatabases WHERE name = @DBName
  
                  --Set recovery to full if it is not already turned on.
                  IF @Recovery &lt;&gt; 'FULL'
                      BEGIN
                          SELECT @cmd = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL'
                          EXEC (@cmd)
                      END
  
                  --Begin Backup
                  SET @Filename = @Path + '\Log' + @DBName + 'Log' +  CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
                  SET @cmd = 'BACKUP LOG ' + @DBName + ' TO DISK = ' + @Filename
                  EXEC (@cmd)
              END
      END
  GO
  </code></pre>