I am receiving an error 'Incorrect syntax near the keyword in

Any ideas?

declare @item varchar(100)
declare @stopid varchar(100)

set @item in (select itemtypeid from laitemtype where itemtypeid like '88BBA7AD-BD25-E011-94E0-001B214A75A5')
set @stopid in (select stopid from lastop where stoptypeid like '7D14FC8C-B184-DD11-9BCC-0014221E87F0')

insert into lalocationitemtyperequest (itemtypeid,requestprocesseddate,createddate, stopid) values (@item,getDate(),getDate(),@stopid)

Recommended Answers

All 2 Replies

declare @item varchar(100)
declare @stopid varchar(100)

set @item in (select itemtypeid from laitemtype where itemtypeid like '88BBA7AD-BD25-E011-94E0-001B214A75A5')
---Right here was incorrect even if it did work because I actually was setting everything to this itemtypeid therefore not needing the 'in' syntax.

set @stopid in (select stopid from lastop where stoptypeid like '7D14FC8C-B184-DD11-9BCC-0014221E87F0')

insert into lalocationitemtyperequest (itemtypeid,requestprocesseddate,createddate, stopid) values (@item,getDate(),getDate(),@stopid)

Here with my corrected statement it is basically just an insert statement but since I am using a subquery I do not require the 'values' syntax.

insert into lalocationitemtyperequest (itemtypeid,requestprocesseddate,createddate, stopid) 
select '90BBA7AD-BD25-E011-94E0-001B214A75A5', getdate(),getdate(),stopid from lastop where stoptypeid = '7D14FC8C-B184-DD11-9BCC-0014221E87F0'

"Set" is a value assignment. Therefore it should have "=" instead of "in", like so:

SET @item = (SELECT itemtypeid FROM laitemtype WHERE itemtypeid LIKE '88BBA7AD-BD25-E011-94E0-001B214A75A5')

However, if you get more than one itemtypeid that meets the criteria, you'll get an error.

That being said, I'm still not sure what you're trying to accomplish here, as the selection criteria for each resulting "set" statement is hard-coded with the value you're querying for. If it's really "LIKE" that you're looking for then you will potentially fall afoul of the multiple results for a single value assignment. There doesn't seem to be any relationship between the tables that I can deduce. If the assistance above isn't enough to handle your problem, can you post more information, please?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.