执行“sp_executesql @sqlcommand"语法错误 [英] Executing a "sp_executesql @sqlcommand" Syntax Error

问题描述

我正在设置一个 SQL 脚本，该脚本根据变量名称创建数据库，然后使用新创建的数据库并从 .bak 文件中恢复它.我在我正在设置的命令之一中遇到了一些语法问题，想问问是否有人可以帮助我发现我的语法错误?我只会粘贴我有问题的代码片段及其声明，如果我是正确的，问题在于我声明文件名路径的方式.我已经尝试设置变量的路径，但由于撇号的位置，我仍然收到错误.谢谢！！！

I am setting up a SQL script that creates a database from a variable name, and then takes that newly created database and restores it from a .bak file. I am having issues with some syntax in one of my commands that I am setting up, and wanted to ask if anybody could help me spot my syntax error? I am only going to paste my troubled snippet of code and its declarations, and if I am correct the issue lies in the way that I am declaring the file name paths. I have tried setting the paths to variables, but I still received errors due to the apostrophe placement. Thanks!!!

declare @DBname varchar(10), @sqlcommand Nvarchar(max)
set @DBname = 'testdb'

创建数据库的代码，并将新数据库设置为单用户模式

Code to create database, and set new database to single user mode

--restore database
set @sqlcommand = N'Restore DATAbase ' + @DBname + ' from disk = ''C:/loc_smartz_db0_template.bak'' with move ' 
+ @DBname + ' to ''C:/ProgramFiles/Microsoft SQL Server/MSSQL/Data/TestDatabase1.mdf'', move ' +     @DBname + ' to ''C:/ProgramFiles/Microsoft SQL Server/MSSQL/Data/TestDatabase1.ldf'', Replace'
EXECUTE sp_executesql @sqlcommand 

将数据库设置回多用户的代码，并打印数据库已成功创建

Code that sets database back to multiuser, and prints that the database was successfully created

推荐答案

看起来之前的海报已经解决了您的问题，但如果您以最佳实践"的方式使用动态 sql，则可能会避免这种情况.将字符串作为变量和字符串文字的混合体连接在一起并不理想，因为它会使使用撇号变得困难(如此处所示).

It looks like the previous posters have fixed your problem, but this may have been avoided if you had used dynamic sql in the 'best practice' manner. Concatenating the string together as a mixture of variables and string literals is not ideal as it makes working with apostrophes difficult (as shown here).

更好的方法是把你的sql写成

A better way is to write your sql as

declare @DBname nvarchar(255) = 'testdb'
        ,@BakName nvarchar(255) = 'C:\loc_smartz_db0_template.bak'
        ,@MovemdfName nvarchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDatabase1.mdf'
        ,@MoveldfName nvarchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDatabase1.ldf'
        ,@sqlcommand nvarchar(max)
        ,@paramList nvarchar(max)

set @paramList = '@DBname nvarchar(255), @BakName nvarchar(255), @MovemdfName nvarchar(255), @MoveldfName nvarchar(255)'
set @sqlcommand = N'Restore DATAbase @DBname from disk = @BakName with move @DBname to @MovemdfName, move @DBname to @MoveldfName, Replace'

exec sp_executesql @statement = @sqlcommand
                  ,@params = @paramList
                  ,@DBname = @DBname
                  ,@BakName = @BakName 
                  ,@MovemdfName  = @MovemdfName 
                  ,@MoveldfName = @MoveldfName 

这样，您的 sql 命令就非常易于阅读和维护.请注意，如果您的路径名中有空格，您也不必纠结于转义变量值中的撇号.

This way, your sql command is very easy to read and maintain. Note that you don't have to mess around with escaping the apostrophes in the variable values either if you have spaces in your pathnames.

它还具有允许 SQL Server 重用执行计划以提高性能的优点(如果您在存储过程中有代码).

It also has the advantage (if you have the code in a stored proc) of allowing SQL Server to reuse execution plans which will improve performance.

请参阅此处了解更多信息信息.

See here for more information.

这篇关于执行“sp_executesql @sqlcommand"语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！