如何传递路径到存储过程? [英] How to pass a path to a stored procedure?

问题描述

我的问题在标题中，我已经定义了以下存储过程

My question is in my title, I have defined a stored procedure with the following

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

当我想运行以下命令时:

When I want to run the following command:

[dbo].[sp_doStuff] 'D:\FolderName\', 'MyDatabase', 'F';

它给了我这个错误:

第15级州立1行1的消息102
'D:'附近的语法不正确.
消息105，级别15，状态1，第1行
字符串"后的引号引起来.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D:'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

有人知道为什么会出错吗?我可以通过将路径硬编码到过程中来使其工作，但我希望能够将其传递给参数.

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.

推荐答案

尝试

' TO DISK = ''' + @path + @databaseName

等...

这篇关于如何传递路径到存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！