消息102 级别15 状态1 服务器,Sql过程 - 错误消息102，级别15，状态1，行56

最新推荐文章于 2023-11-29 16:10:58 发布

庄明仁

最新推荐文章于 2023-11-29 16:10:58 发布

阅读量2.4k

点赞数

文章标签：消息102 级别15 状态1 服务器

我收到以下错误

Msg 102，Level 15，State 1，Line 56

'p'附近的语法不正确。

当我尝试运行下面的SQL过程时。此程序适用于我的程序中的搜索区域，它允许用户提交某些标准，然后使用该标准来提取用户正在查找的所有数据。

-- Add the parameters for the stored procedure here

@logging_ref as varchar(50) = NULL,

@summit_ac_no as varchar(50) = NULL,

@contract_no as varchar(50) = NULL,

@invoice_no as varchar(50) = NULL,

@paycert as varchar(50) = NULL,

@record_type as int = NULL,

@qs as varchar(50) = NULL,

@records as int = NULL,

@state as int = NULL

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = N'

SELECT TOP(@records)

r.r_id as ref,

grossVal as gross,

payCert as cert,

p.p_id as paid,

-- Data Columns for Display --

logRef as "Logging Reference",

db_recTypes.recordName AS "Record Type",

invNo as "Invoice No.",

invDate as "Invoice Date",

accNo as "Summit Account No.",

db_accountNo.name as "Company Name",

contract as "Contract No.",

taxStatus as "Tax Status",

netVal as "Net Value",

vat as "V.A.T",

grossVal as "Gross Value",

paycert as "Payment Certificate No.",

period as "Period",

paydate as "Anticipated Payment Date",

db_qs.name as "QS record sent to",

sentDate as "Date sent to QS",

db_sentMethod.name as "Sent Via",

returnedDate as "Date Returned",

r.deleted as Removed,

lastModified as "Last Modified",

creationDate as "Date Created",

db_users.name as "Creation User",

p.date as "Date Paid"

-- Main Table to Reference --

FROM db_records as r

-- Proceed with SQL JOINs --

JOIN db_recTypes

ON db_recTypes.recordID = recType

Join db_accountNo

ON db_accountNo.com_id = accNo

Join db_qs

On db_qs.q_id = sentTo

JOIN db_sentMethod

On db_sentMethod.v_id = sentVia

Join db_users

On db_users.u_id = R.u_id

LEFT JOIN db_payments as p

ON p.r_id = r.r_id

WHERE 1 = 1 '

IF (@logging_ref IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND logRef LIKE ''%'' + @logging_ref + ''%'

IF (@summit_ac_no IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND accNo LIKE ''%'' + @summit_ac_no + ''%'

IF (@contract_no IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND contract LIKE ''%'' + @contract_no + ''%'

IF (@invoice_no IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND invNo LIKE ''%'' + @invoice_no + ''%'

IF (@paycert IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND payCert LIKE ''%'' + @paycert + ''%'

IF (@record_type IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND recType LIKE ''%'' + @record_type + ''%'

IF (@qs IS NOT NULL) SET @SQL = @SQL + CHAR(13) + CHAR(10) + N'AND db_qs.name LIKE ''%'' + @qs + ''%'

DECLARE @lastToken NVARCHAR(100) =

(

CASE

WHEN @state = 1 THEN ' r.deleted = 0'

WHEN @state = 2 THEN ' p.date IS NOT NULL'

WHEN @state = 3 THEN ' p.date IS NULL'

WHEN @state = 4 THEN ' r.deleted = 1'

END

)

SET @SQL = @SQL + CHAR(13) + CHAR(10) + @lastToken

EXEC sp_executesql @SQL,

N'@logging_ref as varchar(50),

@summit_ac_no as varchar(50),

@contract_no as varchar(50),

@invoice_no as varchar(50),

@paycert as varchar(50),

@record_type as integer,

@qs as varchar(50),

@records as int, @state as int',

@records = @records,

@logging_ref = @logging_ref,

@summit_ac_no = @summit_ac_no,

@contract_no = @contract_no,

@invoice_no = @invoice_no,

@paycert = @paycert,

@record_type = @record_type,

@qs = @qs,