Incorrect syntax near '@Sites'.

I have the following code but it keeps erroring on the last line and I'm unsure as to why it is doing it?

Here is the error message

Msg 102, Level 15, State 1, Line 42

Incorrect syntax near '@.Sites'.

declare @.Sites varchar(50)

declare @.Kit_No char(20)

declare @.Location char(2)

set @.Location = 'Ho'

set @.Kit_No = 'mo1k'

if (SELECT sitetype from gss.dbo.kup_regions where region_code = @.Location) = 10

begin

set @.Sites = '''Pe'',''Hg'',''Vo'',''' + @.Location + ''''

end

select

KR.Region_Code,

KR.Region_Name,

Z.Qty,

Z.Kit_Description,

Z.BookedOutToDate as Usage,

Z.Local_Cost,

(select overstock from gss.dbo.vGss_overstock where region_code = Z.region_code and kit_no = 'm01k' )as Rolling_Avg,

C.Symbol,

Z.FOB,

(SELECT

Price

FROM

gss.dbo.FedEx_Rates Fed

WHERE

SourceRegion = (SELECT Region_Code FROM gss.dbo.KUP_Regions WHERE Region_Name = 'penistone')

AND Weight = (SELECT MAX (Weight) FROM gss.dbo.FedEx_Rates WHERE Weight < (SELECT Packed_Unit_Weight From gss.dbo.KUP_Kits WHERE Kit_Code = (select Kit_Code from gss.dbo.GSS_Kits where Kit_No = 'm01k' ))+0.5)

AND Fed.DestRegion=KR.Region_Code) as Fedex_Price

from

(gss.dbo.kup_regions KR with (nolock)

left outer join

(select KRD.Qty,KRD.BookedOutToDate,KRD.Local_Cost,KRD.FOB,GK.Kit_Description,KRD.Region_code,KRD.Archive_Date

from gss.dbo.kup_region_data KRD with (nolock) inner join gss.dbo.gss_kits GK

on KRD.kit_code = GK.kit_code where GK.kit_no =@.Kit_No and KRD.archive_date is not null )Z

on KR.region_code = Z.region_code)

inner join gss.dbo.Currency C with (nolock) on C.Country_Code = KR.Country_Code

where KR.ExpectExtract = 1 and KR.Designation = 'p' and KR.Region_code in @.Sites

Many thanks for any help

The IN keyword requires a parenthesis, modify the last line as follows:

where KR.ExpectExtract = 1 and KR.Designation = 'p' and KR.Region_code in (@.Sites)

|||

Simon:

If @.sites is a simple 1-item variable then what carlop says is correct.

However, I assume that @.sites is a "string list" -- that is, it is a list of region codes such as @.Sites = 'rg1,rg2,rg3'. If that is the case there are a couple of solutions. A good read is at website:

http://www.sommarskog.se/arrays-in-sql.html

The "cheap" solution here is to convert your huge select statement into a string and then execute the converted string. This is in fact a simplified overview, but you will also need change this:

KR.Region_code in @.Sites

into this:

' ... KR.Region_code in ( ' + @.Sites + ') '

However, this solution might leave you vulnerable to SQL injection and this is not what I would do.

I would either create a function to list out your string list or I would just process your "string list" and load each separate entry into a table variable and change your IN portion into selecting the region codes out of the list.

(BLEAH my comments sound like a bunch of mumbo-jumbo. Let me find an example.)

Dave

|||

-- --
-- Use a "stringList" function to enumerate the parts of the "@.sites" string
-- --

declare @.sites varchar (20) set @.sites = 'N,A'

declare @.kup_regions table (Region_code char (1), Region_Name varchar (25))
insert into @.kup_regions values ('N', 'Northern Region')
insert into @.kup_regions values ('E', 'Eastern Region')
insert into @.kup_regions values ('W', 'West Region')
insert into @.kup_regions values ('S', 'South Region')
insert into @.kup_regions values ('A', 'A non-conforming Region')

-- An example using an inner join -
select a.region_code,
a.region_name
from @.kup_regions a
inner join stringList (',', @.sites) b
on b.entry = left (a.region_code,1)

-- An example using an "IN" clause
select a.region_code,
a.region_name
from @.kup_regions a
where a.region_code in
(select entry from stringList (',', @.sites) )

-- -
-- Sample Output
-- -

-- region_code region_name
-- -- -
-- N Northern Region
-- A A non-conforming Region