Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: Incorrect syntax near

    Project Description: Need to run a table (tbl1) of information (ex. Name,ID, 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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    @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
    You need to DECLARE these variables first, the use SET to give them a value
    Code:
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    7

    Incorrect syntax near

    I apologize, did not send the full code with my problem. I did declare the variables in the upper part of the procedure. Also create the exception table and the columns in it. Do you have any other ideas with this additional information?

  4. #4
    Join Date
    Jan 2012
    Posts
    7

    Incorrect syntax near

    Wim -
    Can you help once again???????
    I tried your suggestion with SET on each line, still same error. I have tried a few other ideas and have the current code in full as follows with the following 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'.

    CURRENT CODE:

    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

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If you change the things I have marked in red, the error message you spoke of will go away, as well as the next error message. And those two error messages will be replaced by a gazillion other error messages.

    Code:
    SELECT * FROM [dbo].[tblImport] ci
    CREATE TABLE [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 [dbo].[tblState] cs
    	INNER JOIN [dbo].[tblModules] cm
    		ON cs.TRACKID = cm.trackID
    	INNER JOIN [dbo].[tblTrack] css
    		ON cs.TRACKID = css.TRACKID
    	INNER JOIN [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 [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 [dbo].[tblModules]
    	SET cm.decimal02 = ci.ZING
    	WHERE cs.TRACKID = ci.TRACKID
    		AND cs.ID = ci.ID
    		AND CID = 'XXXXXXXX'
    
    	UPDATE [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
    	SET @hld_track = ci.TRACKID
    	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
    
    	INSERT INTO [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
    Your code puzzles me. I have never seen anything like this before:
    Code:
    SELECT *
    FROM [dbo].[tblImport] ci
    
    IF ci.TRACKID = cs.TRACKID /* matching record */
    	AND ci.ID = cs.ID
    	AND cs.GDAT <= @LateDate
    	AND cs.GDAT >= @EarlDate
    First you read a whole table, with possibly thousands of records, then you pick one record out at random and compare that to another random record from a JOIN. Is this T-SQL of SQL Server? This won't even compile.

    I tried it with a simple table.
    Code:
    SELECT *
    FROM [dbo].[books] ci
    
    print ci.id
    It gave me this error message:
    Msg 128, Level 15, State 1, Line 4
    The name "ci.id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
    I don't even know where to start to clean up that code.

    This will never work:
    Code:
    	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
    This is the way to assign column values to variables:
    Code:
    SELECT @hld_track = ci.TRACKID,
    	@hld_gdat = ci.GDAT,
    	@hld_id = ci.ID,
    	@hld_l_name = ci.LNAME,
    	@hld_f_name = ci.FNAME
    FROM [dbo].[tblImport] ci
    WHERE Ci.ID = 1
    When the SELECT returns more than one row, the @variables will hold the values of the last record. If you don't use an ORDER BY clause, MSSQL will pick one row out for you, at random.
    Last edited by Wim; 03-14-12 at 19:48.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jan 2012
    Posts
    7
    Wim,
    Response to your response.
    #1 Your response was:
    Your code puzzles me. I have never seen anything like this before:
    SELECT *
    FROM [dbo].[tblImport] ci

    IF ci.TRACKID = cs.TRACKID /* matching record */
    AND ci.ID = cs.ID
    AND cs.GDAT <= @LateDate
    AND cs.GDAT >= @EarlDate
    #1 My response:
    I found some code to take GDAT 30 days earlier and 30 days later than the GDAT. I need to set a date range based off of the GDAT todetermine if I will update a record OR write the record to an exception table. The code I found is:

    SELECT GETDATE(), 'cs.BYE'
    UNION ALL
    SELECT DATEADD(DAY, -30, GETDATE()), '30 Days Earlier'
    DECLARE @EarlDate datetime
    SET @EarlDate = DATEADD(DAY, -30, GETDATE())
    SELECT GETDATE(), 'cs.BYE'
    UNION ALL
    SELECT DATEADD(DAY, 30, GETDATE()), '30 Days Later'
    DECLARE @LateDate datetime
    SET @LateDate = DATEADD(DAY, 30, GETDATE())

    SELECT @EarlDate
    ,@LateDate

    Will this not work? I have tried research to figure out how to do this task. This was the only thing I could find. Please let me know if I am on the right track!

    ***************************
    Response #2

    #2 Your response was:
    First you read a whole table, with possibly thousands of records, then you pick one record out at random and compare that to another random record from a JOIN. Is this T-SQL of SQL Server? This won't even compile.

    #2 My response:
    I am using SQL Server, and am very new at it (obviously). I do have a long back ground in programming just not with SQL. My logic was to SELECT and INTER JOIN based off a record in the import table. Once I had that data I want to either update a record or write a record to an exception table. After I have that procedure running correctly I was going to use a cursor in my procedure to handle each record in the import table.

    Response #3
    #3 Your response was:
    This is the way to assign column values to variables

    SELECT @hld_track = ci.TRACKID,
    @hld_gdat = ci.GDAT,
    @hld_id = ci.ID,
    @hld_l_name = ci.LNAME,
    @hld_f_name = ci.FNAME
    FROM [dbo].[tblImport] ci
    WHERE Ci.ID = 1
    #3 My response:
    Thank-you for this info I have tried SET and SELECT methods At this point in the code. Reach has told me SELECT is the way to go here.

    Any other help would be much appreciated.
    Thank-you

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It's hard to make anything out of that code. To my eye, the code consists of a number of SQLs thrown on a heap without any logical connection. I gave it my best shot. You will most likely have to adjust the queries to make them work or optimise them. But at least it will give you a starting point.

    Using cursors to get something done in SQL is a last resort. Cursors are slow and lock your system. They are bad. Try to get things done without them.
    Code:
    DECLARE @EarlDate datetime
    SET @EarlDate = DATEADD(DAY, -30, CAST(GETDATE() as DATE))
    PRINT @EarlDate
    
    DECLARE @LateDate datetime
    SET @LateDate = DATEADD(DAY, 30, CAST(GETDATE() as DATE))
    PRINT @LateDate
    
    DECLARE @CID VARCHAR(20)
    SET @CID =  'XXXXXXXX'
    
    UPDATE U
    SET U.decimal02 = ci.ZING
    FROM [dbo].[tblModules] as U
    	INNER JOIN [dbo].[tblState] cs
    		ON  U.trackID = cs.TRACKID
    	INNER JOIN [dbo].[tblTrack] css
    		ON cs.TRACKID = css.TRACKID
    	INNER JOIN [dbo].[tblSysNotesW] sn
    		ON cm.MixID = sn.MIXERID
    	INNER JOIN [dbo].[tblImport] ci
    		ON ci.TRACKID = cs.TRACKID 
    		AND ci.ID = cs.ID
    		AND cs.GDAT BETWEEN @EarlDate AND @LateDate
    WHERE cs.CID = @CID
    
    UPDATE U
    SET sn.Notes = ci.CDT
    FROM [dbo].[tblSysNotesW] as U
    	INNER JOIN [dbo].[tblModules] as CM
    		ON cm.MixID = U.MIXERID
    	INNER JOIN [dbo].[tblState] cs
    		ON  CM.trackID = cs.TRACKID
    	INNER JOIN [dbo].[tblTrack] css
    		ON cs.TRACKID = css.TRACKID
    	INNER JOIN [dbo].[tblImport] ci
    		ON ci.TRACKID = cs.TRACKID 
    		AND ci.ID = cs.ID
    		AND cs.GDAT BETWEEN @EarlDate AND @LateDate
    WHERE cs.CID = @CID
    
    INSERT INTO [dbo].[tbl_exceptions]
    	(track
    	,gdat
    	,id
    	,l_name
    	,f_name
    	,zing)
    SELECT ci.TRACKID,
    	ci.GDAT,
    	ci.ID,
    	ci.LNAME,
    	ci.FNAME,
    	ci.ZING
    FROM [dbo].[tblSysNotesW] as U
    	INNER JOIN [dbo].[tblModules]
    		ON cm.MixID = sn.MIXERID
    	INNER JOIN [dbo].[tblState] cs
    		ON  cm.trackID = cs.TRACKID
    	INNER JOIN [dbo].[tblTrack] css
    		ON cs.TRACKID = css.TRACKID
    	INNER JOIN [dbo].[tblImport] ci
    		ON ci.TRACKID = cs.TRACKID 
    		AND ci.ID = cs.ID
    		AND cs.GDAT NOT BETWEEN @EarlDate AND @LateDate
    WHERE cs.CID = @CID
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •