'，'附近的语法不正确。使用Exec时

Issue: Incorrect syntax near ','. (Procedure spAddNewDataToHoldingTable, Line 71)

问题：'，'附近的语法不正确。（程序spAddNewDataToHoldingTable，第71行）

Aim: Insert data to Table (which I created via another stored procedure)

目标：将数据插入表（我通过另一个存储过程创建）

Thoughts: I've tried a few combinations of apostrophes and if I get the SQL to run then it means no apostrophe before the first value and therefor an error.

思考：我已经尝试了一些撇号组合，如果我让SQL运行，那么它意味着在第一个值之前没有撇号，因此出现错误。

I hope one apostrophe in the wrong place is all it is.

我希望在错误的地方出现一个撇号。

IF I did not have a variable name I could run without EXEC but I also want to make it as secure.

如果我没有变量名称，我可以在没有EXEC的情况下运行，但我也想让它变得安全。

Code:

码：

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE  [dbo].[spAddNewDataToHoldingTable] 

@sTableName varchar(50),
@sPMNUM varchar(8),
@sUPRN varchar(510) ,
@sSurveyDate Date,
@sVFMDISCIPLINE varchar(4) ,
@sVFMDISCIPLINEELEMENT varchar(510) ,
@sWorkOrder varchar(510) ,
@sNextSurveyDue  Date ,
@sRiskCategory varchar(510) ,
@sDocURL varchar(MAX) ,
@sDocLinks varchar(MAX) ,
@sVINCIRISKLevel varchar(510) ,
@sRemedialActionWO varchar(510) ,
@sRemedialActionDate date,
@sComments varchar(MAX) ,
@sRISKNUM varchar(510) ,
@sRISKOWNER varchar(510) ,
@sSurveyorsComments varchar(MAX) ,
@sRecommendations varchar(510) ,
@sStatusOfIssue int ,
@sClientContract varchar(12) ,
@sJPNUM varchar(12)


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @sTablename_ AS varchar(max)

SET @sTablename_ = LTRIM(RTRIM(@sTableName))

DECLARE @sql NVARCHAR(2500)

SET @sql = '
INSERT INTO ' + @sTablename_  + '
(
PMNUM,
UPRN,
VFMDISPLINE,
VFMDISPLINEELEMENT,
WorkOrder,
RiskCategory,
DocURL,
DocLinks,
VINCIRISKLevel,
RemedialActionWO,
Comments,
RISKNUM,
RISKOWNER,
SurveyorsComments,
Recommendation,
StatusOfIssue,
ClientContract,
JPNUM,
RemedialActionDate  ,
SurveyDate ,
NextSurveyDate  
)

VALUES

('''

+ ''' + QUOTENAME(@sPMNUM) + '''
, ''' + QUOTENAME(@sUPRN) + '''
, ''' + QUOTENAME(@sVFMDISCIPLINE) + '''
, ''' + QUOTENAME(@sVFMDISCIPLINEELEMENT) + '''
, ''' + QUOTENAME(@sWorkOrder) + '''
, ''' + QUOTENAME(@sRiskCategory) + '''
, ''' + QUOTENAME(@sDocURL) + '''
, ''' + QUOTENAME(@sDocLinks) + '''
, ''' + QUOTENAME(@sVINCIRISKLevel) + '''
, ''' + QUOTENAME(@sRemedialActionWO) + '''
, ''' + QUOTENAME(@sComments) + '''
, ''' + QUOTENAME(@sRISKNUM) + '''
, ''' + QUOTENAME(@sRISKOWNER) + '''
, ''' + QUOTENAME(@sSurveyorsComments) + '''
, ''' + QUOTENAME(@sRecommendations) + '''
, ''' + QUOTENAME(@sStatusOfIssue + '''
, ''' + QUOTENAME(@sClientContract) + '''
, ''' + QUOTENAME(@sJPNUM) + '''
, ''' + QUOTENAME(@sRemedialActionDate) + '''
, ''' + QUOTENAME(@sSurveyDate) + '''
, ''' + QUOTENAME(@sNextSurveyDue) + '''
+ ''')'''

EXEC (@sql)

END

Ref:

参考：

SQL Server incorrect syntax near ')' Incorrect syntax near '(' and near '='

SQL Server附近的语法不正确'）'''附近''和''附近的语法不正确

4 个解决方案

#1

You don't need to use quotename() on all of those, it just wraps them in square brackets.

你不需要在所有这些上使用quotename（），它只是将它们包装在方括号中。

Don't forget to convert() your date datatypes to varchar.

不要忘记将日期数据类型转换为（）varchar。

ALTER PROCEDURE  [dbo].[spAddNewDataToHoldingTable] 

@sTableName varchar(50),
@sPMNUM varchar(8),
@sUPRN varchar(510) ,
@sSurveyDate Date,
@sVFMDISCIPLINE varchar(4) ,
@sVFMDISCIPLINEELEMENT varchar(510) ,
@sWorkOrder varchar(510) ,
@sNextSurveyDue  Date ,
@sRiskCategory varchar(510) ,
@sDocURL varchar(MAX) ,
@sDocLinks varchar(MAX) ,
@sVINCIRISKLevel varchar(510) ,
@sRemedialActionWO varchar(510) ,
@sRemedialActionDate date,
@sComments varchar(MAX) ,
@sRISKNUM varchar(510) ,
@sRISKOWNER varchar(510) ,
@sSurveyorsComments varchar(MAX) ,
@sRecommendations varchar(510) ,
@sStatusOfIssue int ,
@sClientContract varchar(12) ,
@sJPNUM varchar(12)


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @sTablename_ AS varchar(max)

SET @sTablename_ = LTRIM(RTRIM(@sTableName))

DECLARE @sql NVARCHAR(2500)

SET @sql = '
INSERT INTO ' + @sTablename_  + '
(
PMNUM,
UPRN,
VFMDISPLINE,
VFMDISPLINEELEMENT,
WorkOrder,
RiskCategory,
DocURL,
DocLinks,
VINCIRISKLevel,
RemedialActionWO,
Comments,
RISKNUM,
RISKOWNER,
SurveyorsComments,
Recommendation,
StatusOfIssue,
ClientContract,
JPNUM,
RemedialActionDate  ,
SurveyDate ,
NextSurveyDate  
)

VALUES

('

+   '''' + @sPMNUM                + ''''
+ ', ''' + @sUPRN                 + ''''
+ ', ''' + @sVFMDISCIPLINE        + ''''
+ ', ''' + @sVFMDISCIPLINEELEMENT + ''''
+ ', ''' + @sWorkOrder            + ''''
+ ', ''' + @sRiskCategory         + ''''
+ ', ''' + @sDocURL               + ''''
+ ', ''' + @sDocLinks             + ''''
+ ', ''' + @sVINCIRISKLevel       + ''''
+ ', ''' + @sRemedialActionWO     + ''''
+ ', ''' + @sComments             + ''''
+ ', ''' + @sRISKNUM              + ''''
+ ', ''' + @sRISKOWNER            + ''''
+ ', ''' + @sSurveyorsComments    + ''''
+ ', ''' + @sRecommendations      + ''''
+ ', ''' + @sStatusOfIssue        + ''''
+ ', ''' + @sClientContract       + ''''
+ ', ''' + @sJPNUM                + ''''
+ ', ''' + convert(varchar(10),@sRemedialActionDate,120)+ ''''
+ ', ''' + convert(varchar(10),@sSurveyDate,120)        + ''''
+ ', ''' + convert(varchar(10),@sNextSurveyDue,120)     + ''''
+ ');'

EXEC (@sql)
END;

#2

You added another '''

你添加了另一个'''

here is updated one

这里更新了一个

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE  [dbo].[spAddNewDataToHoldingTable] 

@sTableName varchar(50),
@sPMNUM varchar(8),
@sUPRN varchar(510) ,
@sSurveyDate Date,
@sVFMDISCIPLINE varchar(4) ,
@sVFMDISCIPLINEELEMENT varchar(510) ,
@sWorkOrder varchar(510) ,
@sNextSurveyDue  Date ,
@sRiskCategory varchar(510) ,
@sDocURL varchar(MAX) ,
@sDocLinks varchar(MAX) ,
@sVINCIRISKLevel varchar(510) ,
@sRemedialActionWO varchar(510) ,
@sRemedialActionDate date,
@sComments varchar(MAX) ,
@sRISKNUM varchar(510) ,
@sRISKOWNER varchar(510) ,
@sSurveyorsComments varchar(MAX) ,
@sRecommendations varchar(510) ,
@sStatusOfIssue int ,
@sClientContract varchar(12) ,
@sJPNUM varchar(12)


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @sTablename_ AS varchar(max)

SET @sTablename_ = LTRIM(RTRIM(@sTableName))

DECLARE @sql NVARCHAR(2500)

SET @sql = '
INSERT INTO ' + @sTablename_  + '
(
PMNUM,
UPRN,
VFMDISPLINE,
VFMDISPLINEELEMENT,
WorkOrder,
RiskCategory,
DocURL,
DocLinks,
VINCIRISKLevel,
RemedialActionWO,
Comments,
RISKNUM,
RISKOWNER,
SurveyorsComments,
Recommendation,
StatusOfIssue,
ClientContract,
JPNUM,
RemedialActionDate  ,
SurveyDate ,
NextSurveyDate  
)

VALUES

(''' + QUOTENAME(@sPMNUM) + '''
, ''' + QUOTENAME(@sUPRN) + '''
, ''' + QUOTENAME(@sVFMDISCIPLINE) + '''
, ''' + QUOTENAME(@sVFMDISCIPLINEELEMENT) + '''
, ''' + QUOTENAME(@sWorkOrder) + '''
, ''' + QUOTENAME(@sRiskCategory) + '''
, ''' + QUOTENAME(@sDocURL) + '''
, ''' + QUOTENAME(@sDocLinks) + '''
, ''' + QUOTENAME(@sVINCIRISKLevel) + '''
, ''' + QUOTENAME(@sRemedialActionWO) + '''
, ''' + QUOTENAME(@sComments) + '''
, ''' + QUOTENAME(@sRISKNUM) + '''
, ''' + QUOTENAME(@sRISKOWNER) + '''
, ''' + QUOTENAME(@sSurveyorsComments) + '''
, ''' + QUOTENAME(@sRecommendations) + '''
, ''' + QUOTENAME(@sStatusOfIssue) + '''
, ''' + QUOTENAME(@sClientContract) + '''
, ''' + QUOTENAME(@sJPNUM) + '''
, ''' + QUOTENAME(@sRemedialActionDate) + '''
, ''' + QUOTENAME(@sSurveyDate) + '''
, ''' + QUOTENAME(@sNextSurveyDue) + ''')'

EXEC (@sql)

END

#3

To escape a single quote you need to double it up. Therefore ''', ''' will be returned as ','

要逃避单一报价，您需要将其加倍。因此'''，'''将返回为'，'

So, your code should look like this:

所以，你的代码应如下所示：

SET @sql = '
INSERT INTO ' + @sTablename_  + '
(
PMNUM,
UPRN,
VFMDISPLINE,
VFMDISPLINEELEMENT,
WorkOrder,
RiskCategory,
DocURL,
DocLinks,
VINCIRISKLevel,
RemedialActionWO,
Comments,
RISKNUM,
RISKOWNER,
SurveyorsComments,
Recommendation,
StatusOfIssue,
ClientContract,
JPNUM,
RemedialActionDate  ,
SurveyDate ,
NextSurveyDate  
)

VALUES

(
''' + QUOTENAME(@sPMNUM) + '''
, ''' + QUOTENAME(@sUPRN) + '''
, ''' + QUOTENAME(@sVFMDISCIPLINE) + '''
, ''' + QUOTENAME(@sVFMDISCIPLINEELEMENT) + '''
, ''' + QUOTENAME(@sWorkOrder) + '''
, ''' + QUOTENAME(@sRiskCategory) + '''
, ''' + QUOTENAME(@sDocURL) + '''
, ''' + QUOTENAME(@sDocLinks) + '''
, ''' + QUOTENAME(@sVINCIRISKLevel) + '''
, ''' + QUOTENAME(@sRemedialActionWO) + '''
, ''' + QUOTENAME(@sComments) + '''
, ''' + QUOTENAME(@sRISKNUM) + '''
, ''' + QUOTENAME(@sRISKOWNER) + '''
, ''' + QUOTENAME(@sSurveyorsComments) + '''
, ''' + QUOTENAME(@sRecommendations) + '''
, ''' + QUOTENAME(@sStatusOfIssue) + '''
, ''' + QUOTENAME(@sClientContract) + '''
, ''' + QUOTENAME(@sJPNUM) + '''
, ''' + QUOTENAME(@sRemedialActionDate) + '''
, ''' + QUOTENAME(@sSurveyDate) + '''
, ''' + QUOTENAME(@sNextSurveyDue) + ''')'

#4

I don't think you should be using dynamic sql here, you should instead be fixing the design that causes you have to need multiple tables all with the exact same schema. However, if you are going to stick with this you need to use parameters in your dynamic sql instead of trying to fight single quotes all over the place. And simply adding a set of double single quotes is only going to delay the problems with this. Here is how you can do this with dynamic sql and parameterize all the values.

我不认为你应该在这里使用动态sql，你应该修复设计，导致你需要使用完全相同的模式的多个表。但是，如果您要坚持使用此功能，则需要在动态sql中使用参数，而不是尝试在整个地方使用单引号。简单地添加一组双单引号只会延迟这个问题。以下是使用动态sql和参数化所有值的方法。

ALTER PROCEDURE  [dbo].[spAddNewDataToHoldingTable] 
(
    @sTableName varchar(50),
    @sPMNUM varchar(8),
    @sUPRN varchar(510) ,
    @sSurveyDate Date,
    @sVFMDISCIPLINE varchar(4) ,
    @sVFMDISCIPLINEELEMENT varchar(510) ,
    @sWorkOrder varchar(510) ,
    @sNextSurveyDue Date ,
    @sRiskCategory varchar(510) ,
    @sDocURL varchar(MAX) ,
    @sDocLinks varchar(MAX) ,
    @sVINCIRISKLevel varchar(510) ,
    @sRemedialActionWO varchar(510) ,
    @sRemedialActionDate date,
    @sComments varchar(MAX) ,
    @sRISKNUM varchar(510) ,
    @sRISKOWNER varchar(510) ,
    @sSurveyorsComments varchar(MAX) ,
    @sRecommendations varchar(510) ,
    @sStatusOfIssue int ,
    @sClientContract varchar(12) ,
    @sJPNUM varchar(12)
) AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(max)

    SET @sql = N'
    INSERT INTO ' + QUOTENAME(LTRIM(RTRIM(@sTableName)))  + '
    (
        PMNUM
        , UPRN
        , VFMDISPLINE
        , VFMDISPLINEELEMENT
        , WorkOrder
        , RiskCategory
        , DocURL
        , DocLinks
        , VINCIRISKLevel
        , RemedialActionWO
        , Comments
        , RISKNUM
        , RISKOWNER
        , SurveyorsComments
        , Recommendation
        , StatusOfIssue
        , ClientContract
        , JPNUM
        , RemedialActionDate
        , SurveyDate
        , NextSurveyDate  
    )
    VALUES
    (
        @sPMNUM
        , @sUPRN
        , @sVFMDISCIPLINE
        , @sVFMDISCIPLINEELEMENT
        , @sWorkOrder
        , @sRiskCategory
        , @sDocURL
        , @sDocLinks
        , @sVINCIRISKLevel
        , @sRemedialActionWO
        , @sComments
        , @sRISKNUM
        , @sRISKOWNER
        , @sSurveyorsComments
        , @sRecommendations
        , @sStatusOfIssue
        , @sClientContract
        , @sJPNUM
        , @sRemedialActionDate
        , @sSurveyDate
        , @sNextSurveyDue
    )'

    EXEC sp_executesql @sql
        , N'@sPMNUM varchar(8)
            , @sUPRN varchar(510)
            , @sVFMDISCIPLINE varchar(4)
            , @sVFMDISCIPLINEELEMENT varchar(510)
            , @sWorkOrder varchar(510)
            , @sRiskCategory varchar(510)
            , @sDocURL varchar(MAX)
            , @sDocLinks varchar(MAX)
            , @sVINCIRISKLevel varchar(510)
            , @sRemedialActionWO varchar(510)
            , @sComments varchar(MAX)
            , @sRISKNUM varchar(510)
            , @sRISKOWNER varchar(510)
            , @sSurveyorsComments varchar(MAX)
            , @sRecommendations varchar(510)
            , @sStatusOfIssue int
            , @sClientContract varchar(12)
            , @sJPNUM varchar(12)
            , @sRemedialActionDate varchar(10)
            , @sSurveyDate varchar(10)
            , @sNextSurveyDue varchar(10)'
        , @sPMNUM = @sPMNUM
        , @sUPRN = @sUPRN
        , @sVFMDISCIPLINE = @sVFMDISCIPLINE
        , @sVFMDISCIPLINEELEMENT = @sVFMDISCIPLINEELEMENT
        , @sWorkOrder = @sWorkOrder
        , @sRiskCategory = @sRiskCategory
        , @sDocURL = @sDocURL
        , @sDocLinks = @sDocLinks
        , @sVINCIRISKLevel = @sVINCIRISKLevel
        , @sRemedialActionWO = @sRemedialActionWO
        , @sComments = @sComments
        , @sRISKNUM = @sRISKNUM
        , @sRISKOWNER = @sRISKOWNER
        , @sSurveyorsComments = @sSurveyorsComments
        , @sRecommendations = @sRecommendations
        , @sStatusOfIssue = @sStatusOfIssue
        , @sClientContract = @sClientContract
        , @sJPNUM = @sJPNUM
        , @sRemedialActionDate = @sRemedialActionDate
        , @sSurveyDate = @sSurveyDate
        , @sNextSurveyDue = @sNextSurveyDue     
END;

#1