Incorrect syntax near 1

[lindasoph]

Can't figure out how to correct this error, also is the commit rollback set up correctly could not find verification or example!!

Project Description: Need to run a table (tbl1) of information (ex. Name,Ssn, Amounts) through some joined tables finding out if a record exists. If a record does exists an update to two tables is needed (the update data is amount field). If a record does not exist then the data from tbl1 will be written to another table (tbl2).

Error is
Incorrect syntax near '@hld_tracknum'.

SQL Code where error is occuring:
SELECT *
FROM [XXXX_dev].[dbo].[tblImport] ci
IF ci.TRACKNUM = cs.TRACKNumber
AND ci.ID = cs.Id
AND cs.GDAT <= @LateDate
AND cs.GDAT >= @EarlDate
BEGIN
UPDATE [XXXX_dev].[dbo].[tblModule]
SET cm.decimal02 = ci.ZING
WHERE cs.TRACNUM = ci.TNumber
AND cs.SPECNum = ci.SPN
UPDATE [XXXX_dev].[dbo].[tblSpecial]
SET sn.Notes = ci.CDT
WHERE CustID = 'XXXX'
AND cm.SpecXX = sn.SpecialXX
END
ELSE
@hld_tracknum = ci.TRACKNUM
@hld_gdat = ci.GDAT
@hld_id = ci.ID
@hld_l_name = ci.LNAME
@hld_f_name = ci.FNAME
@hld_zing = ci.ZING
@hld_cdt= ci.CDT
INSERT INTO [XXXX_dev].[dbo].[xxxx_exceptions]
(col1
,col2
,col3
,col4
,col5
,col6
,col7)
VALUES ('@hld_tracknum'
,'@hld_gdat'
,'@hld_id'
,'@hld_l_name'
,'@hld_f_name'
,'@hld_zing'
,'@hld_cdt')
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO

 

[djj55]

In T-SQL you need a set or select to populate a variable.
The way this is written the ELSE is incorrect and needs its own begin end and SET


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[lindasoph]

I placed the suggested ideas into the code below and received
a new Error:
Incorrect syntax near '@hld_GDAT'.

SQL Code where error is occuring: I also tried a select statement and received the same error. It seems like the if statement is not set up correctly. Could it have something to do with the ending code:
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN

------------------------------------------------------------

SELECT *
FROM [XXXX_dev].[dbo].[tblImport] ci
IF ci.TRACKNUM = cs.TRACKNumber
AND ci.ID = cs.Id
AND cs.GDAT <= @LateDate
AND cs.GDAT >= @EarlDate
BEGIN
UPDATE [XXXX_dev].[dbo].[tblModule]
SET cm.decimal02 = ci.ZING
WHERE cs.TRACNUM = ci.TNumber
AND cs.SPECNum = ci.SPN
UPDATE [XXXX_dev].[dbo].[tblSpecial]
SET sn.Notes = ci.CDT
WHERE CustID = 'XXXX'
AND cm.SpecXX = sn.SpecialXX
END
ELSE
BEGIN
SET
@hld_tracknum = ci.TRACKNUM
@hld_gdat = ci.GDAT
@hld_id = ci.ID
@hld_l_name = ci.LNAME
@hld_f_name = ci.FNAME
@hld_zing = ci.ZING
@hld_cdt= ci.CDT
INSERT INTO [XXXX_dev].[dbo].[xxxx_exceptions]
(col1
,col2
,col3
,col4
,col5
,col6
,col7)
VALUES ('@hld_tracknum'
,'@hld_gdat'
,'@hld_id'
,'@hld_l_name'
,'@hld_f_name'
,'@hld_zing'
,'@hld_cdt')
END

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO

 

[gmmastros]

The way I see it, there are MANY problems here. The error message you are seeing is probably just the tip of the iceberg.

It's likely caused by this part:

[tt]
SET
@hld_tracknum = ci.TRACKNUM
@hld_gdat = ci.GDAT
@hld_id = ci.ID
@hld_l_name = ci.LNAME
@hld_f_name = ci.FNAME
@hld_zing = ci.ZING
@hld_cdt= ci.CDT
[/tt]

You need to use SET for each variable, or switch to using SELECT with commas added in, like this:

    SET @hld_tracknum = ci.TRACKNUM
    SET @hld_gdat = ci.GDAT
    SET @hld_id = ci.ID
    SET @hld_l_name = ci.LNAME
    SET @hld_f_name = ci.FNAME       
    SET @hld_zing = ci.ZING
    SET @hld_cdt= ci.CDT

or

    SELECT           
       @hld_tracknum = ci.TRACKNUM,
       @hld_gdat = ci.GDAT,
       @hld_id = ci.ID,
       @hld_l_name = ci.LNAME,
       @hld_f_name = ci.FNAME,      
       @hld_zing = ci.ZING,
       @hld_cdt= ci.CDT

This block of code will still fail because you are setting variables from (what looks like) the ci table. If you are setting variables from a table, you need to use SELECT instead of SET. However, if you were actually setting variables from a table, this block of code would immediately be followed by a FROM clause, which isn't there.

Unfortunately, I cannot give you better advice because I don't understand the "big picture" of what you are trying to accomplish here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Also, I think the first IF is wrong...unless I'm missing something.

IF ci.TRACKNUM = cs.TRACKNumber
           AND ci.ID = cs.Id
           AND cs.GDAT <= @LateDate
           AND cs.GDAT >= @EarlDate

Where are the tables coming from ci & cs are not defined within the statement and I believe they need to be.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[lindasoph]

Your last statement has told me I need to use a SELECT statement.
So with that code in place I am still getting the same error:
Incorrect syntax near '@hld_gdat'.
Revised code:

SELECT *
FROM [XXXX_dev].[dbo].[tblImport] ci
IF ci.TRACKNUM = cs.TRACKNumber
AND ci.ID = cs.Id
AND cs.GDAT <= @LateDate
AND cs.GDAT >= @EarlDate
BEGIN
UPDATE [XXXX_dev].[dbo].[tblModule]
SET cm.decimal02 = ci.ZING
WHERE cs.TRACNUM = ci.TNumber
AND cs.SPECNum = ci.SPN
UPDATE [XXXX_dev].[dbo].[tblSpecial]
SET sn.Notes = ci.CDT
WHERE CustID = 'XXXX'
AND cm.SpecXX = sn.SpecialXX
END
ELSE
BEGIN
@hld_tracknum = ci.TRACKNUM
@hld_gdat = ci.GDAT
@hld_id = ci.ID
@hld_l_name = ci.LNAME
@hld_f_name = ci.FNAME
@hld_zing = ci.ZING
@hld_cdt = ci.CDT
INSERT INTO [XXXX_dev].[dbo].[xxxx_exceptions]
(col1
,col2
,col3
,col4
,col5
,col6
,col7)
SELECT tblImport.TRACKNUM,
tblImport.GDAT,
tblImport.ID,
tblImport.LNAME,
tblImport.FNAME,
tblImport.ZING,
tblImport.CDT
FROM tblImport
WHERE (tblImport.TRACKNUM = '@hld_tracknum')
AND (tblImport.GDAT = '@hld_gdat')
AND (tblImport.ID = '@hld_id')
AND (tblImport.ZING = '@hld_zing')
END


IF @@ERROR = 0
COMMIT TRAN <<<<--This is the section of code that I don't know is needed
ELSE
ROLLBACK TRAN
GO <<<<--

 

[lindasoph]

The tables and all columns and hold variables are defined.
If you would like me to submit all code I can. It is just alot of work because I have to change names of items due company policies.

 

[JonFer]

Instead of trying to do this record by record, I'd suggest writing 2 update statements based on finding the matching records and then an insert statement based on not finding matching records to process all the rows meeting your criteria. I think it will be more straightforward.

 

[lindasoph]

Thank-you for the information JonFer. Couple of questions regarding forming and IF statement - when creating an IF statement do you place a BEGIN after the IF conditions an END after the block. EXAMPLE:
IF var1 = var2
AND St = State
BEGIN
INSERT INTO table
-----
-----
-----
END
Another Question
Should a COMMIT/ROLLBACK be structured into an IF statement? I am not able to find a good example of this. If so should it be closed with an END? EXAMPLE:
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN

END

 

[markros]

If you plan to only write one statement after IF, the BEGIN/END blocks are optional (but can be added to improve readability). If you want to execute multiple statements, then BEGIN/END blocks are mandatory and without them only the first statement will be executed by the IF block and the rest will always be executed.

You can not test column's values using IF statement. You can only check variables with the IF, for working with data you need to use SET based operations.

If your
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN

should not be END as you don't have corresponding BEGIN block.

Number of END should always match number of opened BEGIN blocks.


PluralSight Learning Library

 

[djj55]

To add to markos' to check column values you can use a CASE statement.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[lindasoph]

Thank-you for the valuable information!

 

[lindasoph]

Can you still help with my error?????

With all the suggestion I have ended up with the following code and still have the errors:
Msg 102, Level 15, State 1, Line 96
Incorrect syntax near '@hld_suta'.
Msg 102, Level 15, State 1, Line 130
Incorrect syntax near 'END'. <<--LAST END IN CODE

This is the full code that was run:

SELECT * FROM [XXX_dev].[dbo].[tblImport] ci
CREATE TABLE [XXX_dev].[dbo].[tbl_exceptions]
(
track varchar(50) not null,
gdat varchar(100) null,
id int not null,
l_name varchar(50),
f_name varchar(50),
zing decimal(9,2),
);
DECLARE @hld_track varchar(50), @hld_gdat varchar, @hld_id INT
DECLARE @hld_l_name varchar (50), @hld_f_name varchar (50)
DECLARE @hld_zing decimal (9,2)


SELECT css.TRACKNumber
,cs.GDAT
,cs.ID
,cs.TRACKID
,cs.CID
,cs.MixID
,cm.decimal02
,sn.notes
FROM [XXX_dev].[dbo].[tblState] cs
INNER JOIN [XXX_dev].[dbo].[tblModules] cm
ON cs.TRACKID = cm.trackID
INNER JOIN [XXX_dev].[dbo].[tblTrack] css
ON cs.TRACKID = css.TRACKID
INNER JOIN [XXX_dev].[dbo].[tblSysNotesW] sn
ON cm.MixID = sn.MIXERID
WHERE cs.CID = 'XXXXXXXX'
ORDER BY
cs.ID
,cs.GDAT
,cs.TRACKID
,cs.MixID
/* Procedure to set-up checking GDAT within 60 day range */
SELECT GETDATE(), 'cs.GDAT'
UNION ALL
SELECT DATEADD(DAY, -30, GETDATE()), '30 Days Earlier'
DECLARE @EarlDate datetime
SET @EarlDate = DATEADD(DAY, -30, GETDATE())
SELECT GETDATE(), 'cs.GDAT'
UNION ALL
SELECT DATEADD(DAY, 30, GETDATE()), '30 Days Later'
DECLARE @LateDate datetime
SET @LateDate = DATEADD(DAY, 30, GETDATE())

SELECT @EarlDate
,@LateDate

SELECT *
FROM [XXX_dev].[dbo].[tblImport] ci
IF ci.TRACKID = cs.TRACKID /* matching record */
AND ci.ID = cs.ID
AND cs.GDAT <= @LateDate
AND cs.GDAT >= @EarlDate

BEGIN
UPDATE [XXX_dev].[dbo].[tblModules]
SET cm.decimal02 = ci.ZING
WHERE cs.TRACKID = ci.TRACKID
AND cs.ID = ci.ID
AND CID = 'XXXXXXXX'
UPDATE [XXX_dev].[dbo].[tblSysNotesW]
SET sn.Notes = ci.CDT
WHERE CID = 'XXXXXXXX'
AND cm.MixID = sn.MIXERID
END
IF ci.TRACKID <> cs.TRACKID /* no matching record */
AND ci.ID <> cs.ID
AND cs.GDAT >= @LateDate
AND cs.GDAT <= @EarlDate
BEGIN
@hld_track = ci.TRACKID
@hld_gdat = ci.GDAT
@hld_id = ci.ID
@hld_l_name = ci.LNAME
@hld_f_name = ci.FNAME
@hld_zing = ci.ZING
INSERT INTO [XXX_dev].[dbo].[tbl_exceptions]
(track
,gdat
,id
,l_name
,f_name
,zing)
SELECT tblImport.TRACKID,
tblImport.GDAT,
tblImport.ID,
tblImport.LNAME,
tblImport.FNAME,
tblImport.ZING,
tblImport.CDT
FROM tblImport
WHERE (tblImport.TRACKID = '@hld_track')
AND (tblImport.GDAT = '@hld_gdat')
AND (tblImport.ID = '@hld_id')
AND (tblImport.ZING = '@hld_zing')
END

 

[markros]

You're missing SET keywords in this block of code

@hld_track = ci.TRACKID
@hld_gdat = ci.GDAT
@hld_id = ci.ID
@hld_l_name = ci.LNAME
@hld_f_name = ci.FNAME
@hld_zing = ci.ZING

Each should begin with SET.



PluralSight Learning Library

 

[lindasoph]

Thank-you,
I placed SET as suggested: SET @hld_tracknum = ci.TRACKNUM
SET @hld_gdat = ci.GDAT
SET @hld_id = ci.ID
etc........

NOW I have received the following error:

Msg 4104, Level 16, State 1, Line 79
The multi-part identifier "ci.TRACKNUM" could not be bound.
Msg 4104, Level 16, State 1, Line 79
The multi-part identifier "cs.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 79
The multi-part identifier "ci.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 79
The multi-part identifier "cs.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 79
The multi-part identifier "cs.GDAT" could not be bound.
Msg 4104, Level 16, State 1, Line 79
The multi-part identifier "cs.GDAT" could not be bound.
Msg 4104, Level 16, State 1, Line 85
The multi-part identifier "cs.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 85
The multi-part identifier "ci.TRACKID" could not be bound.
Msg 4104, Level 16, State 1, Line 85
The multi-part identifier "cs.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 85
The multi-part identifier "ci.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 85
The multi-part identifier "cs.CID" could not be bound.
Msg 4104, Level 16, State 1, Line 90
The multi-part identifier "cs.CID" could not be bound.
Msg 4104, Level 16, State 1, Line 90
The multi-part identifier "cm.MixID" could not be bound.
Msg 4104, Level 16, State 1, Line 90
The multi-part identifier "sn.MIXERID" could not be bound.
Msg 4104, Level 16, State 1, Line 96
The multi-part identifier "ci.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 96
The multi-part identifier "cs.SUTAID" could not be bound.
Msg 4104, Level 16, State 1, Line 96
The multi-part identifier "ci.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 96
The multi-part identifier "cs.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 96
The multi-part identifier "cs.GDAT" could not be bound.
Msg 4104, Level 16, State 1, Line 96
The multi-part identifier "cs.GDAT" could not be bound.
Msg 4104, Level 16, State 1, Line 101
The multi-part identifier "ci.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 102
The multi-part identifier "ci.GDAT" could not be bound.
Msg 4104, Level 16, State 1, Line 103
The multi-part identifier "ci.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 104
The multi-part identifier "ci.LNAME" could not be bound.
Msg 4104, Level 16, State 1, Line 105
The multi-part identifier "ci.FNAME" could not be bound.
Msg 4104, Level 16, State 1, Line 108
The multi-part identifier "ci.ZING" could not be bound.
Msg 4104, Level 16, State 1, Line 109
The multi-part identifier "ci.CDT" could not be bound.

 

[markros]

Well, in your code I still see IF cl.SomeField = cm.SomeField which is invalid. Also, your update statements seem wrong - they refer to aliases not used by the UPDATE command.

PluralSight Learning Library

 

[lindasoph]

I do not understand why IF cl.SomeField = cm.SomeField is not valid. Can you please explain a little more?

 

[JonFer]

Below is an example of doing all updates to "cm" table for matching records in a SQL statement - just fix the matching criteria in the joins and where clauses. The same idea can be used to make all updates to the "sn" table for matching records. All inserts for non-matching records can be run with 1 SQL statement as well.

UPDATE cm SET cm.decimal02 = ci.ZING
From [XXX_dev].[dbo].[tblModules] cm
Inner Join
[XXX_dev].[dbo].[tblState] cs
On cs.TRACKID = cm.trackID
Inner Join
[XXX_dev].[dbo].[tblImport] ci
On cs.TRACKID = ci.TRACKID And
cs.ID = ci.ID
Where cs.GDAT <= @LateDate And
cs.GDAT >= @EarlDate And
cm.CID = 'XXXXXXXX'

I would not suggest doing this record by record. However, if you want to do that, you should review the use of CURSORs (example at:

http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/).

 

[markros]

IF is a procedural code. As such, it can not operate with the table rows, it can only operate with variables. If you need to operate with table row values, you need to use set-based operation such as SELECT.

PluralSight Learning Library