MarcusIT Support MarcusIT Support - 5 months ago 21
SQL Question

Sql Procedure - Error Msg 102, Level 15, State 1, Line 56

I'm getting the following error


Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'p'.


when I try to run my SQL Procedure seen below. This Procedure is for a search area on my program, it allows the user to submit certain critera which it then uses to pull out all the data the user is looking for.

-- 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
AS
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,
@records = @records,
@state = @state
END
GO


Also does any one now how to get SQL Server 2014 to display the query that has been executed so that I can read what the query looks like?

PRINTED Query



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
AND p.date IS NULL

Answer

@lastToken misses + ' AND ' + @lastToken

and @records argument of sp_executesql two times mentioned