How handle Comma Delimited list in WHERE Clause

[rvancleef]

I am new to SQL Server.

I have a form that allows the user to select one or more status values for a project search (The user can search for all projects that are 1> Cancelled 3> Deferred 2> Closed). These selections are passed to my search stored procedure currently as a varchar comma delimited list (1,4,6).

Question 1 => Is a comma delimited list the best way to get the selections into the where clause?

Question 2 => How can I build the appropriate SELECT statement to return the projects given this list of status'?

When I try to supply the list to the where clause manually, the query works.

SELECT * FROM PROJECT WHERE StatusID IN (1,3,2)

When I supply the list ot the WHERE clause via a variable, it fails with a data type error.
DECLARE @ProjStat varchar (10)
SELECT @ProjStat = '1,3,2'
SELECT * FROM PROJECT WHERE StatusID IN (@ProjStat)

 

[JayKusch]

Problem here is that StatusID is an Integer, most likely, and you are comparing it to @ProjStat which is a VarChar. May want to try this ...

DECLARE @ProjStat varchar (10)
DECLARE @Command varchar(100)

SELECT @ProjStat = '1,3,2'

SELECT @Command = 'SELECT * FROM PROJECT WHERE StatusID IN (' + RTRIM(LTRIM(@ProjStat)) + ')'

EXEC @Command



Thanks

J. Kusch

 

[rvancleef]

Yes, the StatusID is an "int".

Still getting data type error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value ' + RTRIM(LTRIM(@ProjStat)) + ' to a column of data type smallint.

 

[JayKusch]

Sorry ... should be

EXEC (@Command)

Thanks

J. Kusch

 

[rvancleef]

Thank you! I am now getting the correct query results.

What is the significance of the EXEC() statement? Why can't one run the SQL Statement with the string handling directly?

 

[rvancleef]

I am trying to integrate this solution into the full code of the stored procedure and getting an error declaring the @ProjID variable. Am I implementing the Exec() style inappropriately?:

DECLARE @ProjID int
DECLARE @ProjStatus varchar(10)
DECLARE @StartDate1 smalldatetime
DECLARE @StartDate2 smalldatetime
DECLARE @EndDate1 smalldatetime
DECLARE @EndDate2 smalldatetime
DECLARE @Command varchar(1500)

SELECT @ProjID = NULL
SELECT @ProjStatus = '2,4,1'
SELECT @StartDate1 = NULL
SELECT @StartDate2 = NULL
SELECT @EndDate1 = NULL
SELECT @EndDate2 = NULL

SELECT @Command = '
SELECT Project.ProjID,Project.ProjectName,Status.Status,Phase.PhaseName,Project.QueueOrder,ITStaff.Title,ITStaff.FName,ITStaff.LName,Project.ProjStartDate,Project.ProjEndDate
FROM Project
LEFT JOIN Status ON Project.StatusID=Status.StatusID
LEFT JOIN Phase ON Project.PhaseID=Phase.PhaseID
LEFT JOIN ITStaff ON Project.ManagedBy=ITStaff.Staff_ID
WHERE ProjID = COALESCE(@ProjID,ProjID) AND
Project.StatusID IN (' + RTRIM(LTRIM(@ProjStatus)) + ')AND
Project.ProjStartDate BETWEEN COALESCE(@StartDate1,Project.ProjStartDate)AND COALESCE(@StartDate2,Project.ProjStartDate) AND
Project.ProjEndDate BETWEEN COALESCE(@EndDate1,Project.ProjEndDate) AND COALESCE(@EndDate2,Project.ProjEndDate)
ORDER BY Project.ProjID'
EXEC (@Command)

 

[ClaireHCM]

Try this ,but not sure whether it works or not

SELECT @Command = '
SELECT Project.ProjID,Project.ProjectName,Status.Status,Phase.PhaseName,
Project.QueueOrder,ITStaff.Title,ITStaff.FName,ITStaff.LName,Project.ProjStartDate,Project.ProjEndDate
FROM Project
LEFT JOIN Status ON Project.StatusID=Status.StatusID
LEFT JOIN Phase ON Project.PhaseID=Phase.PhaseID
LEFT JOIN ITStaff ON Project.ManagedBy=ITStaff.Staff_ID
WHERE ProjID = COALESCE('+LTRIM(RTRIM(@ProjID))+',ProjID) AND
Project.StatusID IN ('+ RTRIM(LTRIM(@ProjStatus))+')AND
Project.ProjStartDate BETWEEN COALESCE('+RTRIM(LTRIM(@StartDate1))+',Project.ProjStartDate)AND
COALESCE('+RTRIM(LTRIM(@StartDate2))+',Project.ProjStartDate) AND
Project.ProjEndDate BETWEEN COALESCE('+RTRIM(LTRIM(@EndDate1))+',Project.ProjEndDate) AND
COALESCE('+RTRIM(LTRIM(@EndDate2))+',Project.ProjEndDate)
ORDER BY Project.ProjID'

are youe @startdates and @enddats character datafield?
If it datetime field ,you need to convert them into character field then only you can concatenate them!

 

[rvancleef]

The statement is executed without error, but the results are wrong:

If I search on a valid ProjectID only, there are no records in the results grid. Same thing if I search for a valid list of StatusID's.

Could this have to do with the StartDate/EndDates (which are smalldatetime)?

 

[ClaireHCM]

change all the @statusid,@startdate,@enddate to
convert(varchar(25),@statsid)
convert(varchar(25),@startdate)
convert(varchar(25),@enddate)
and try again

 

[rvancleef]

Same result: The Statements executes without error, but the results incorrectly show no results


DECLARE @ProjID int
DECLARE @ProjStatus varchar(10)
DECLARE @StartDate1 smalldatetime
DECLARE @StartDate2 smalldatetime
DECLARE @EndDate1 smalldatetime
DECLARE @EndDate2 smalldatetime
DECLARE @Command varchar(1500)

SELECT @ProjID = NULL
SELECT @ProjStatus = '1,3,5'
SELECT @StartDate1 = NULL
SELECT @StartDate2 = NULL
SELECT @EndDate1 = NULL
SELECT @EndDate2 = NULL

SELECT @Command = '
SELECT Project.ProjID,Project.ProjectName,Status.Status,Phase.PhaseName,
Project.QueueOrder,ITStaff.Title,ITStaff.FName,ITStaff.LName,Project.ProjStartDate,Project.ProjEndDate
FROM Project
LEFT JOIN Status ON Project.StatusID=Status.StatusID
LEFT JOIN Phase ON Project.PhaseID=Phase.PhaseID
LEFT JOIN ITStaff ON Project.ManagedBy=ITStaff.Staff_ID
WHERE ProjID = COALESCE('+LTRIM(RTRIM(@ProjID))+',ProjID) AND
Project.StatusID IN ('+ RTRIM(LTRIM(convert(varchar(25),@ProjStatus)))+')AND
Project.ProjStartDate BETWEEN COALESCE('+RTRIM(LTRIM(convert(varchar(25),@startdate1)))+',Project.ProjStartDate)AND
COALESCE('+RTRIM(LTRIM(convert(varchar(25),@startdate2)))+',Project.ProjStartDate) AND
Project.ProjEndDate BETWEEN COALESCE('+RTRIM(LTRIM(convert(varchar(25),@enddate1)))+',Project.ProjEndDate) AND
COALESCE('+RTRIM(LTRIM(convert(varchar(25),@enddate2)))+',Project.ProjEndDate)
ORDER BY Project.ProjID'
EXEC (@Command)

 

[JayKusch]

Try This ...

DECLARE @ProjID int
DECLARE @ProjStatus varchar(10)
DECLARE @StartDate1 smalldatetime
DECLARE @StartDate2 smalldatetime
DECLARE @EndDate1 smalldatetime
DECLARE @EndDate2 smalldatetime
DECLARE @Command varchar(1500)

SELECT @ProjID = NULL
SELECT @ProjStatus = '1,3,5'
SELECT @StartDate1 = NULL
SELECT @StartDate2 = NULL
SELECT @EndDate1 = NULL
SELECT @EndDate2 = NULL

SELECT @Command = '
SELECT Project.ProjID,Project.ProjectName,Status.Status,Phase.PhaseName,
Project.QueueOrder,ITStaff.Title,ITStaff.FName,ITStaff.LName,Project.ProjStartDate,Project.ProjEndDate
FROM Project
LEFT JOIN Status ON Project.StatusID=Status.StatusID
LEFT JOIN Phase ON Project.PhaseID=Phase.PhaseID
LEFT JOIN ITStaff ON Project.ManagedBy=ITStaff.Staff_ID
WHERE ProjID = COALESCE('+LTRIM(RTRIM(Convert(Char,@ProjID)))+',ProjID) AND
Project.StatusID IN ('+ RTRIM(LTRIM(convert(varchar(25),@ProjStatus)))+')AND
Project.ProjStartDate BETWEEN COALESCE('+'''' + RTRIM(LTRIM(convert(varchar(25),@startdate1))) + '''' +',Project.ProjStartDate)AND
COALESCE('+ '''' + RTRIM(LTRIM(convert(varchar(25),@startdate2)))+ '''' + ',Project.ProjStartDate) AND
Project.ProjEndDate BETWEEN COALESCE(' + '''' +RTRIM(LTRIM(convert(varchar(25),@enddate1)))+ '''' + ',Project.ProjEndDate) AND
COALESCE('+ '''' + RTRIM(LTRIM(convert(varchar(25),@enddate2)))+ '''' + ',Project.ProjEndDate)
ORDER BY Project.ProjID'

print @Command

--EXEC (@Command)


Thanks

J. Kusch

 

[rvancleef]

Same issue. No error but no results

 

[ClaireHCM]

I think you need to check whether there is records there which match all these conditions or not.

 

[JamesLean]

The problem is probably because you are not giving all the parameters a value. Therefore when you are concatenating them to the @command string, you end up with a NULL string as the result, which obviously isn't going to return any records(!).

In JayKusch's example, he has a PRINT command to check the resultant command that will be executed - is this printing correctly or are you not getting anything (I assume the latter)?

You need to bring the COALESCE outside the @command value:

... WHERE ProjID = ' + COALESCE(@ProjID, 'ProjID') + ' AND ...

--James