MSSQL Server Incorrect syntax near ','

Question

I am using sql server and am trying to see how I can set a variable to a list of locations like such:

Declare @StartDate datetime,
        @EndDate datetime,
        @EmpName varchar(50),
        @LOCID varchar(25);

Set @StartDate = '2016/01/01';
Set @EndDate = '2019/02/01';
Set @EmpName = 'David Brierton';
Set @LOCID =  ('desoto_tickets','ccc_tickets');

select 
  ID,
  requester_name,
  requester_id,
  COUNT(*) as numberOfTickets
from Tickets
where created_at between @StartDate and @EndDate
  and requester_name = @EmpName
  and ',' + tags + ',' like '%,' + @LOCID + ',%'
group by Id, requester_name, requester_id

I am getting an error for the comma in Set @LOCID = ('desoto_tickets','ccc_tickets');

Error states:

Incorrect syntax near ','.

What am I doing wrong here?

Any help would be greatly appreciated!

Edit: In the database in the tags column each tag is listed like this: desoto_counter,desoto_tickets,new_associate_workchecked,new_hire__paperwork_check__transaction_complete___express,newhire_assistance,no_error,ops,walk

Each row has a list of tags like this in the database.

New Edit:

    Declare @StartDate datetime,
        @EndDate datetime,
    --  @EmpName varchar(50),
        @LOCID1 varchar(25),
        @LOCID2 varchar(25),
        @LOCID3 varchar(25),
        @LOCID4 varchar(25),
        @LOCID5 varchar(25),
        @LOCID6 varchar(25);

Set @StartDate = '2016/01/01';
Set @EndDate = '2019/02/01';
-- Set @EmpName = 'David Brierton';
Set @LOCID1 =  'desoto_counter';
Set @LOCID2 =  'lwr_counter';
Set @LOCID3 =  'nr_tickets';
Set @LOCID4 =  'ps_tickets';
Set @LOCID5 =  'sdl_tickets';
Set @LOCID5 =  'ccc_tickets';

select 
  ID,
  requester_name,
  requester_id,
  COUNT(*) as numberOfTickets,
  case
when tags like '%,' + @LOCID1 + ',%' then 'CDS'
     tags like '%,' + @LOCID2 + ',%' then 'CLR'
     tags like '%,' + @LOCID3 + ',%' then 'CNR'
     tags like '%,' + @LOCID4 + ',%' then 'CPS'
     tags like '%,' + @LOCID5 + ',%' then 'SDL'
     tags like '%,' + @LOCID6 + ',%' then 'CCC'
else 'Unknown'
end [Location],
from Tickets
where created_at between @StartDate and @EndDate
 -- and requester_name = @EmpName
  and ',' + tags + ',' like '%,' + @LOCID1 + ',%'
  or ',' + tags + ',' like '%,' + @LOCID2 + ',%'
  or ',' + tags + ',' like '%,' + @LOCID3 + ',%'
  or ',' + tags + ',' like '%,' + @LOCID4 + ',%'
  or ',' + tags + ',' like '%,' + @LOCID5 + ',%'
  or ',' + tags + ',' like '%,' + @LOCID6 + ',%'
group by Id, requester_name, requester_id

Answer 1

Going to expand on my comment. Firstly, the syntax you are using is:

SET @LOCID =  ('desoto_tickets','ccc_tickets');

This isn't valid value as a scalar variable can only contain 1 value. If you want to store a delimited string, then it needs to be 1 (literal) value:

SET @LOCID =  'desoto_tickets,ccc_tickets';

However, the WHERE clause you have isn't ideal, specifically:

AND ',' + tags + ',' LIKE '%,' + @LOCID + ',%'

Instead of using syntax like that, I suggest using a splitter. if you're on SQL Server 2016+, you can use STRING_SPLIT:

SELECT ID,
       requester_name,
       requester_id,
       COUNT(*) AS numberOfTickets
FROM Tickets T
     CROSS APPLY STRING_SPLIT(@LOCID,',') SS
WHERE created_at BETWEEN @StartDate AND @EndDate
  AND requester_name = @EmpName
  AND SS.[value] = T.Tags --Assumes tags isn't delimited either, if it is, then your data is flawed
GROUP BY Id,
         requester_name,
         requester_id;

If you're on SQL Server 2008, search delimitedsplit8k, and if you're on 2012/2014 then delimitedsplit8k_lead.

Alternatively, you can use a table variable:

DECLARE @Loc table (tag varchar(50));
INSERT INTO @Loc
VALUES('desoto_tickets'),('ccc_tickets');

SELECT ID,
       requester_name,
       requester_id,
       COUNT(*) AS numberOfTickets
FROM Tickets T
     JOIN @Loc L ON T.tags = L.Tag --Assumes tags isn't delimited either, if it is, then your data is flawed
WHERE created_at BETWEEN @StartDate AND @EndDate
  AND requester_name = @EmpName
GROUP BY Id,
         requester_name,
         requester_id;

Edit: Ok, the OP's data is flawed. I will update this answer to fix their data shortly.

Edit 2: The below "fixes" the OP's data. Please note the Cleanup near the end AND the DROP. To the OP, make sure you fix ALL your references before you DROP the column tags:

USE Sandbox;
GO
--Create a sample table
CREATE TABLE dbo.Tickets (ID int IDENTITY,
                          RequesterID int,
                          Tags varchar(MAX));
INSERT INTO dbo.Tickets (RequesterID,
                         Tags)
VALUES (1,'desoto_counter,desoto_tickets,new_associate_workchecked,new_hire__paperwork_check__transaction_complete___express,newhire_assistance,no_error,ops,walk'),
       (2,'newhire_assistance,new_associate_workchecked,no_error');
GO
--Create 2 additional tables
CREATE TABLE dbo.Tags (ID int IDENTITY,
                       TagName varchar(100))

CREATE TABLE dbo.TicketTags (TicketTagID int IDENTITY,
                             TicketID int, --Should be a FOREIGN KEY
                             TagID int); --Should be a FOREIGN KEY

GO
--Get the unique tags. This might be slow if you have a lot of tickets

INSERT INTO dbo.Tags (TagName)
SELECT DISTINCT [value]
FROM tickets t
     CROSS APPLY STRING_SPLIT(Tags,',') SS; --Assumes 2016+ see prior part of answer if not
GO
--Now populate the relational table

INSERT INTO dbo.TicketTags (TicketID,
                            TagID)
SELECT T.ID,
       tags.ID
FROM dbo.tickets T
     CROSS APPLY STRING_SPLIT(Tags,',') SS --Assumes 2016+ see prior part of answer if not
     JOIN dbo.Tags ON SS.[value] = tags.TagName;

GO

--Then drop tbhe old column, which you might want to do LATER
ALTER TABLE dbo.Tickets DROP COLUMN tags;
GO

--And now your "new" query.

DECLARE @Loc table (tag varchar(50));
INSERT INTO @Loc
VALUES('desoto_tickets'),('ccc_tickets');

SELECT *
FROM tickets t
WHERE EXISTS (SELECT 1
              FROM dbo.TicketTags TT
                   JOIN dbo.Tags ON TT.TagID = dbo.Tags.ID
                   JOIN @Loc L ON tags.TagName = L.tag
               WHERE TT.TicketID = T.ID);
GO

--Clean up
DROP TABLE dbo.TicketTags;
DROP TABLE dbo.Tags;
DROP TABLE dbo.Tickets