Help with stored procedure

You wrote that column a.tags contains comma-separated values. So, for example, one field in a.tags might contain 'text1,text2,text3' or something like that, correct?

If that is right, then you shouldn't be using the "a.tags IN (SELECT value FROM dbo.Split(@tags, ','))" portion of code.

Let's say that the user chose two tags to filter his query: text1 and text2. Your IN clause, as currently constructed, says, "Is 'text1,text2,text3' in 'text1'?" No. Then, "Is 'text1,text2,text3' in 'text2'?" And again the answer is no.

In other words, you are treating 'text1,text2,text3' as one value, then trying to compare that one composite value to individual values like 'text1' then 'text2'. That won't work. Does what I'm writing make sense given what you know about the results you're getting back? Am I missing anything?

Given what I just wrote, I find it hard to believe that things are working correctly for numeric values. I suppose if a.tags contains only *one* value the filter will work, but that doesn't have to do with numeric vs non-numeric. Does that make sense?

I guess. How would I need to change the sp then ?

Best answer would be to change the database so that a.tags becomes a tags table. Can you do that?

I tried saving the id of each tag. I already did that but then I have problem displaying the tag text
Can't I just use the text to search then ?

What if you try using a creative EXISTS clause that basically says: if there is *any* tag in a.tags that matches a tag in @tags, then return this row? This might do that for you (it replaces the "( @tags IS NULL OR @tags = '' OR a.tags IN (SELECT value FROM dbo.Split(@tags, ',')))" section of code):

( @tags IS NULL OR @tags = '' OR exists (
	select count(*) from dbo.Split(a.tags, ',') as tbl
		join (SELECT value FROM dbo.Split(@tags, ',')) as web
		on tbl.value = web.value
		having count(*) > 0
	))

Select allOpen in new window

I guess it doesn't matter if I use text or the id. Right ?  How can I compare multiple values from a forms value against multiple values in the database ?

Will that work with text ?

Well, you're storing *all* values as text in a.tags, so it should work for any values.

I guess I should ask, does your dbo.Split() function deal with preceding or trailing spaces? Ideally, the function should treat "   text1" as equal to "text1", right?

This is the split function:

USE [BlueDot]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 07/31/2016 22:08:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[Split] (@list nvarchar(max), @delim nchar(1) = ',')

returns table as

return
   with csvtbl(start, [stop]) as (
     select start = convert(bigint, 1), [stop] = charindex(@delim collate slovenian_bin2, @list + @delim)
     union all
     select start = [stop] + 1, [stop] = charindex(@delim collate slovenian_bin2, @list + @delim, [stop] + 1)
     from   csvtbl
     where  [stop] > 0
  )
  select ltrim(rtrim(substring(@list, start, case when [stop] > 0 then [stop] - start else 0 end))) as value
  from   csvtbl
  where  ([stop] > 0)
  and (ltrim(rtrim(substring(@list, start, case when [stop] > 0 then [stop] - start else 0 end))) <> '')

Select allOpen in new window

I got an error message with the code below.

This is the error:

Msg 102, Level 15, State 1, Procedure report_basic_cases_06_results, Line 43
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Procedure report_basic_cases_06_results, Line 44
Incorrect syntax near the keyword 'as'.

Select allOpen in new window

This is the code:

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[report_basic_cases_06_results]    Script Date: 07/31/2016 20:17:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[report_basic_cases_06_results]
    (
      @userid VARCHAR(MAX),
      @start_date DATE = NULL,
	  @end_date DATE = NULL,
	  @tags VARCHAR(MAX) = NULL ,
	  @keyword VARCHAR(250)= NULL

    )
AS
    SELECT  
			a.CaseID ,
            a.Visible ,
            a.Updatedby ,
            a.Lastupdate ,
            a.SectionCase ,
            a.FirmId ,
            a.Importance ,
            a.VisibleEmployer ,
            a.tags,
            b.UserId AS attyid ,
            b.MailStr + ' ' + b.MaidenNm AS atty ,
            c.id,
            c.CaseId ,
            c.Archived ,
            d.FirstNm + ' ' + d.LastNm AS Alien,
            g.description AS casestatus,
            a.Comments
          
    FROM    dbo.Casecomments a
            LEFT JOIN Users AS b ON b.UserId = a.Updatedby
            INNER JOIN Cases AS c ON c.Id = a.CaseID
            INNER JOIN Users AS d ON d.UserId = c.AlienId
            INNER JOIN Cases AS h ON h.id = a.caseid
            INNER JOIN admin_casestatus AS g ON h.Archived = g.idstatus
    WHERE   a.SectionCase = 'Case'
            AND c.Archived <> 1 
            AND a.Updatedby LIKE @userid
            AND (@start_date is null or a.Lastupdate >= @start_date) and (@end_date is null or a.Lastupdate < dateadd(d,1,@end_date))
            AND ( @tags IS NULL OR @tags = '' OR exists (select count(*) from dbo.Split(a.tags, ',') as tbl JOIN (SELECT value FROM dbo.Split(@tags, ',')) as web on tbl.value = web.value HAVING count(*) > 0))
            AND  ((RTRIM(@keyword) IS NULL) OR (a.Comments LIKE  '%' + @keyword + '%'))
              
    ORDER BY a.Lastupdate DESC;
        
       

Select allOpen in new window

Looks good. The "ltrim(rtrim(" piece means you won't have to worry about extraneous spaces.

Hmmm. Don't see anything immediately, but maybe put the "value" field in square brackets, since it's a keyword? So, change would be like this:

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[report_basic_cases_06_results]    Script Date: 07/31/2016 20:17:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[report_basic_cases_06_results]
    (
      @userid VARCHAR(MAX),
      @start_date DATE = NULL,
	  @end_date DATE = NULL,
	  @tags VARCHAR(MAX) = NULL ,
	  @keyword VARCHAR(250)= NULL

    )
AS
    SELECT  
			a.CaseID ,
            a.Visible ,
            a.Updatedby ,
            a.Lastupdate ,
            a.SectionCase ,
            a.FirmId ,
            a.Importance ,
            a.VisibleEmployer ,
            a.tags,
            b.UserId AS attyid ,
            b.MailStr + ' ' + b.MaidenNm AS atty ,
            c.id,
            c.CaseId ,
            c.Archived ,
            d.FirstNm + ' ' + d.LastNm AS Alien,
            g.description AS casestatus,
            a.Comments
          
    FROM    dbo.Casecomments a
            LEFT JOIN Users AS b ON b.UserId = a.Updatedby
            INNER JOIN Cases AS c ON c.Id = a.CaseID
            INNER JOIN Users AS d ON d.UserId = c.AlienId
            INNER JOIN Cases AS h ON h.id = a.caseid
            INNER JOIN admin_casestatus AS g ON h.Archived = g.idstatus
    WHERE   a.SectionCase = 'Case'
            AND c.Archived <> 1 
            AND a.Updatedby LIKE @userid
            AND (@start_date is null or a.Lastupdate >= @start_date) and (@end_date is null or a.Lastupdate < dateadd(d,1,@end_date))
            AND ( @tags IS NULL OR @tags = '' OR exists (select count(*) from dbo.Split(a.tags, ',') as tbl JOIN (SELECT [value] FROM dbo.Split(@tags, ',')) as web on tbl.[value] = web.[value] HAVING count(*) > 0))
            AND  ((RTRIM(@keyword) IS NULL) OR (a.Comments LIKE  '%' + @keyword + '%'))
              
    ORDER BY a.Lastupdate DESC;

Select allOpen in new window

same error

Can you tell which lines are line 43 and line 44?

The error is:

Msg 102, Level 15, State 1, Procedure report_basic_cases_06_results, Line 42
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Procedure report_basic_cases_06_results, Line 42
Incorrect syntax near the keyword 'as'.

Select allOpen in new window

Line 42 is:              INNER JOIN Users AS d ON d.UserId = c.AlienId

which makes no sense. If I remove the tag line we added the SP works fine

You can copy and paste the code on a notepad and see that the above is line 42 but doesn't have any 'as' or the other character which are causing the issue

Let's make it simple. How about trying this code:

declare @tags varchar(50) = 'text3, text2'

select *
	from dbo.CaseComments a
	where (
		@tags IS NULL OR @tags = '' OR 
		exists (
			select count(*) from dbo.Split(a.tags, ',') as tbl JOIN (SELECT [value] FROM dbo.Split(@tags, ',')) as web on tbl.[value] = web.[value] HAVING count(*) > 0
			)
		)

Select allOpen in new window

You'll need to change 'text3, text2' to recognizable values for a.tags

That gives me an error as well. I am using MS SQL 2008 R2

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'as'.

Select allOpen in new window

Version shouldn't be a problem. Let's simplify even more:

declare @tags varchar(50) = 'text3, text2'

select *
	from dbo.CaseComments a
	where exists (
			select count(*) from dbo.Split(a.tags, ',') as tbl 
				having count(*) > 0
			)

Select allOpen in new window

Now I get this:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

How about re-typing the code? As far as I can see, it should run clean on 2008R2.

Same issue. For some reason the word 'EXISTS' is greyed out

DECLARE @tags VARCHAR(50) = 'text3, text2';

SELECT  *
FROM    dbo.Casecomments a
WHERE   EXISTS ( SELECT COUNT(*)
                 FROM   dbo.Split(a.tags, ',') AS tbl
                 HAVING COUNT(*) > 0 );

Select allOpen in new window

Error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

Select allOpen in new window

Capture.PNG

The greyed out is fine--just T-SQL syntax highlighting. What happens when you try this:

DECLARE @tags VARCHAR(50) = 'text3, text2';

SELECT  *
FROM    dbo.Casecomments a
WHERE   EXISTS ( SELECT COUNT(*)
                 FROM   dbo.Split(@tags, ',') AS tbl
                 HAVING COUNT(*) > 0 );

Select allOpen in new window

I made a change to this and got no error:

DECLARE @tags VARCHAR(50) = 'Travel Date, Contact method';

SELECT  *
FROM    dbo.Casecomments a
WHERE   EXISTS ( SELECT COUNT(*) FROM   dbo.Split(@tags, ',') AS tbl HAVING COUNT(*) > 0 )
ORDER BY comid DESC

Select allOpen in new window

But It didn't filter anything out. I got ALL results.

Can you script out your dbo.Casecomments table and post the code? It's not clear to me why you can't run the code.

It just ran, only it didn't filter out the results, no errors on your last sql.

This is the create script for that table

USE [BlueDot]
GO

/****** Object:  Table [dbo].[Casecomments]    Script Date: 07/31/2016 23:24:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Casecomments](
	[COMID] [INT] IDENTITY(1,1) NOT NULL,
	[CaseID] [NUMERIC](18, 0) NULL,
	[Visible] [BIT] NULL,
	[Comments] [VARCHAR](MAX) NULL,
	[Updatedby] [NUMERIC](18, 0) NULL,
	[Lastupdate] [DATETIME] NULL,
	[CommBy] [BIT] NULL,
	[SectionCase] [NVARCHAR](50) NULL,
	[FirmId] [INT] NULL,
	[Importance] [INT] NULL,
	[VisibleEmployer] [BIT] NULL,
	[SectionName] [NVARCHAR](50) NULL,
	[tags] [NVARCHAR](250) NULL,
 CONSTRAINT [PK_Casecomments] PRIMARY KEY CLUSTERED 
(
	[COMID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Casecomments] ADD  CONSTRAINT [DF_Casecomments_Visible]  DEFAULT ((0)) FOR [Visible]
GO

ALTER TABLE [dbo].[Casecomments] ADD  CONSTRAINT [DF_Casecomments_Lastupdate]  DEFAULT (GETDATE()) FOR [Lastupdate]
GO

ALTER TABLE [dbo].[Casecomments] ADD  CONSTRAINT [DF_Casecomments_CommBy]  DEFAULT ((0)) FOR [CommBy]
GO

ALTER TABLE [dbo].[Casecomments] ADD  CONSTRAINT [DF_Casecomments_SectionCase]  DEFAULT (N'Case') FOR [SectionCase]
GO

Select allOpen in new window

Last suggestion for today. Just try using your dbo.Split on a.tags like this:

select a.COMID, a.CaseID, s.[value]
	from dbo.Casecomments as a
	cross apply dbo.Split (a.tags, ',') s

Select allOpen in new window

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

Ill check back tomorrow. The previous statement showed no error but it didn't filter results.

Thx. I'll wait for tomorrow then

For the time being I had to shelf this