sql-server - 字符串 'BDR_POST-BI ( SCT' 后的非闭合引号

Question

我有之前可以顺利运行的存储程序。但是现在，当我尝试运行它时，会出现错误“消息 105，级别 15，状态 1，第 84 行字符串'BDR_POST-BI（SCT'）后的未闭合引号。消息102，级别15，状态1，行84 'BDR_POST-BI (SCT'."附近的语法不正确。顺便说一下，这是我的存储过程

USE [dbbib]
GO
/****** Object:  StoredProcedure [dbo].[sp_QuerySummary]    Script Date: 10/4/2018 4:13:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_QuerySummary]
AS
BEGIN
DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(FailureMode as varchar) + ']',
'[' + cast(FailureMode as varchar)+ ']')
FROM (SELECT    distinct    FailureMode FROM tblBIB )as s
GROUP BY FailureMode
order by FailureMode

DECLARE @columns2 VARCHAR(8000)

SELECT @columns2 = COALESCE(@columns2 + ',isnull([' + cast(FailureMode as varchar) + '],0) as ['+ cast(FailureMode as varchar) +']',
'isnull([' + cast(FailureMode as varchar)+ '],0) as ['+ cast(FailureMode as varchar)+']')
FROM (SELECT    distinct    FailureMode FROM tblBIB )as s
GROUP BY FailureMode
order by FailureMode

DECLARE @columns3 VARCHAR(8000)

SELECT @columns3 = COALESCE(@columns3 + '+isnull([' + cast(FailureMode as varchar) + '],0)',
'isnull([' + cast(FailureMode as varchar)+ '],0)')
FROM (SELECT    distinct    FailureMode FROM tblBIB where FailureMode not like 'QUARANTINE' and FailureMode not like 'BARE' and FailureMode not like 'PM' and FailureMode not like 'TEMPORARY HOLD_SET B')as s
GROUP BY FailureMode
order by FailureMode

DECLARE @query VARCHAR(8000)

SET @query = '

declare @table table (BibType nvarchar(100), CntActive int)

insert into @table
select BibType,count(BibID) as CntActive from(
select substring(BibID,1,6) as BibType, BibID from(
select * ,
case
when len(BibID)>1   and len(BIBRecall)>1 then 1
when len(BibID)=1   and len(BIBRecall)>1 then 1
else 0
end as selection
from(
SELECT        isnull(a.BibID,0) as BibID, isnull(tblBIB.BIBRecall,0)as BIBRecall
FROM            (SELECT DISTINCT BibID
                          FROM            Orca.dbo.tblLatestTurnAllBIB
                          WHERE        (CONVERT(date, DTDone) = CONVERT(date, GETDATE()))) AS a full outer JOIN
                         tblBIB ON a.BibID = tblBIB.BIBRecall
)as s
)as s where selection=0
)as q group by BibType



declare @table2 table ( CntActive int, rn int)

insert into @table2
select count(BibID) as CntActive ,0 as rn from(
select substring(BibID,1,6) as BibType, BibID from(
select * ,
case
when len(BibID)>1   and len(BIBRecall)>1 then 1
when len(BibID)=1   and len(BIBRecall)>1 then 1
else 0
end as selection
from(
SELECT    isnull(a.BibID,0) as BibID, isnull(tblBIB.BIBRecall,0)as BIBRecall
FROM            (SELECT DISTINCT BibID
                          FROM            Orca.dbo.tblLatestTurnAllBIB
                          WHERE        (CONVERT(date, DTDone) = CONVERT(date, GETDATE()))) AS a full outer JOIN
                         tblBIB ON a.BibID = tblBIB.BIBRecall
)as s
)as s where selection=0
)as q 


select*,row_number()over(order by total desc) as rn from(
SELECT Device,DeviceType,'+ @columns3 +' as Total,isnull(q.CntActive,0) as CntActive,'+ @columns2 +',q.BibType
FROM (SELECT        a.DeviceType, tbldevtypev2.Device, a.Cnt, a.FailureMode
FROM            (SELECT        DeviceType, COUNT(BIBRecall) AS Cnt, FailureMode
                          FROM            tblBIB
                          GROUP BY DeviceType, FailureMode) AS a left outer JOIN
                         tbldevtypev2 ON a.DeviceType = tbldevtypev2.[BIB Type]
)as a
PIVOT
(
MAX(Cnt)
FOR [FailureMode]
IN (' + @columns + ')
)
AS a left outer  join @table q on a.DeviceType = q.BibType
)as p 


union  all


SELECT ''Grand Total'' as Device,'''' as DeviceType,'+@columns3+' as Total,*,'''' as BibType  from(
Select isnull(i.CntActive,0) as CntActive,o.* from(
SELECT '+@columns2+',0 as rn
FROM (
SELECT        COUNT(BIBRecall) AS Cnt, FailureMode
                          FROM            tblBIB
						  
                          GROUP BY  FailureMode 
)as a
PIVOT
(
MAX(Cnt)
FOR [FailureMode]
IN (' + @columns + ')
)
AS p
)as o full outer join @table2 i on o.rn= i.rn
)as p

'

EXECUTE(@query)
END

标签： sql-servervb.net

Answer 1

将数据插入数据库时​​，请考虑使用SQL 参数以避免SQL 注入：

cmd.Parameters.Add("@line", SqlDbType.VarChar).Value = line 

我也会考虑实施Using：

有时您的代码需要非托管资源，例如文件句柄、COM 包装器或 SQL 连接。Using 块保证在您的代码使用完这些资源时处理一个或多个此类资源。这使它们可供其他代码使用。

Using con As New SqlConnection(str),
      cmd As New SqlCommand("INSERT INTO table2 ([a], [roll], [c]) VALUES (1, 2, @line)", con)

    cmd.Parameters.Add("@line", SqlDbType.VarChar).Value = line

    con.Open()

    cmd.ExecuteNonQuery()
  End Using

我还会考虑循环遍历Using语句中的文件以保存一遍又一遍地创建 SQL 对象：

Using con As New SqlConnection(str),
      cmd As New SqlCommand("INSERT INTO table2 ([a], [roll], [c]) VALUES (1, 2, @line)", con)
    cmd.Parameters.Add("@line", SqlDbType.VarChar)

    con.Open()

        Using sr As New StreamReader(path)
            Do While sr.Peek() >= 0
                cmd.Parameters("@line").Value = sr.ReadLine
                cmd.ExecuteNonQuery()
            Loop
        End Using
    End Using
End Sub

_{这段代码未经测试，我没有环境，但它应该给你一些可以使用的东西。}