title: How to pass a path to a stored procedure?
body: <p>My question is in my title, I have defined a stored procedure with the following</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sp_doStuff]   
         @path CHAR(256), @databaseName sysname, @backupType CHAR(1)  
  AS  
  BEGIN 
     SET NOCOUNT ON;  
  
     DECLARE @sqlCommand NVARCHAR(1000)  
     DECLARE @dateTime NVARCHAR(20)  
  
     SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +  
     REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   
  
     IF @backupType = 'F'  
             SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +  
             ' TO DISK = ' + @path + @databaseName + '_Full_' + @dateTime + '.BAK'''  
  
     IF @backupType = 'D'  
             SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +  
             ' TO DISK = ' + @path + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL' 
  
     IF @backupType = 'L'  
             SET @sqlCommand = 'BACKUP LOG ' + @databaseName +  
             ' TO DISK = ' + @path + @databaseName + '_Log_' + @dateTime + '.TRN'''  
  
     EXECUTE sp_executesql @sqlCommand  
  END
  </code></pre>
  
  <p>When I want to run the following command:</p>
  
  <pre><code>[dbo].[sp_doStuff] 'D:\FolderName\', 'MyDatabase', 'F';
  </code></pre>
  
  <p>It gives me this error:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 1<br>
    Incorrect syntax near 'D:'.<br>
    Msg 105, Level 15, State 1, Line 1<br>
    Unclosed quotation mark after the character string ''.</p>
  </blockquote>
  
  <p>Does anyone have a clue why this gives errors? I can make it work by hard coding the path into the procedure, but I want to be able to pass it in a a parameter.</p>