Help with stored procedure

I have a form that passes multiple values separated by a comma, example:

Agreement, Contact method, Follow up

This are used to create a report using a Stored Procedure ( I am using ASP Classic). The stored procedure uses a split function and compares the values stored in the database (also comma separated) with the ones passed by the form. When using numbers this seems to work fine, but when passing text values it doesn't.  Perhaps its just a matter of syntax. Note that if there are no tags selected then it will return all records (which is OK)

The parameter passed is called: tags
The field to compare it against is:  a.tags

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 a.tags IN (SELECT value FROM dbo.Split(@tags, ',')))
            AND  ((RTRIM(@keyword) IS NULL) OR (a.Comments LIKE  '%' + @keyword + '%'))
              
    ORDER BY a.Lastupdate DESC;

Open in new window


This is the code in my ASP file used to call the SP and filter the values:

Dim sp_reporting__userid
sp_reporting__userid = ""
if(request.form("userid")   <> "") then sp_reporting__userid = request.form("userid")  

Dim sp_reporting__start_date
sp_reporting__start_date = null
if(request.form("comment_start")   <> "") then sp_reporting__start_date = request.form("comment_start")  

Dim sp_reporting__end_date
sp_reporting__end_date = null
if(request.form("comment_end")   <> "") then sp_reporting__end_date = request.form("comment_end")  

Dim sp_reporting__tags
sp_reporting__tags = ""
if(request.form("tags") <> "") then sp_reporting__tags = request.form("tags")

Dim sp_reporting__keyword
sp_reporting__keyword = null
if(request.form("keyword")    <> "") then sp_reporting__keyword = request.form("keyword")  

%>

<%

set sp_reporting = Server.CreateObject("ADODB.Command")
sp_reporting.ActiveConnection = MM_bluedot_STRING
sp_reporting.CommandText = "dbo.report_basic_cases_06_results"
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@RETURN_VALUE", 3, 4)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@userid", 200, 1,500,sp_reporting__userid)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@start_date", 135, 1,10,sp_reporting__start_date)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@end_date", 135, 1,10,sp_reporting__end_date)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@tags", 200, 1,5000,sp_reporting__tags)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@keyword", 200, 1,100,sp_reporting__keyword)
sp_reporting.CommandType = 4
sp_reporting.CommandTimeout = 0
sp_reporting.Prepared = true
set report_results = sp_reporting.Execute
report_results_numRows = 0

Open in new window


Should anything in the code be changed or the SP  so that it works with text values?
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bcnagelCommented:
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?
AleksAuthor Commented:
I guess. How would I need to change the sp then ?
bcnagelCommented:
Best answer would be to change the database so that a.tags becomes a tags table. Can you do that?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

AleksAuthor Commented:
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 ?
bcnagelCommented:
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
	))

Open in new window

AleksAuthor Commented:
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 ?
AleksAuthor Commented:
Will that work with text ?
bcnagelCommented:
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?
AleksAuthor Commented:
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))) <> '')

Open in new window

AleksAuthor Commented:
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'.

Open 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;
        
       

Open in new window

bcnagelCommented:
Looks good. The "ltrim(rtrim(" piece means you won't have to worry about extraneous spaces.
bcnagelCommented:
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;

Open in new window

AleksAuthor Commented:
same error
bcnagelCommented:
Can you tell which lines are line 43 and line 44?
AleksAuthor Commented:
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'.

Open 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
bcnagelCommented:
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
			)
		)

Open in new window


You'll need to change 'text3, text2' to recognizable values for a.tags
AleksAuthor Commented:
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'.

Open in new window

bcnagelCommented:
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
			)

Open in new window

AleksAuthor Commented:
Now I get this:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
bcnagelCommented:
How about re-typing the code? As far as I can see, it should run clean on 2008R2.
AleksAuthor Commented:
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 );

Open in new window


Error:

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

Open in new window

Capture.PNG
bcnagelCommented:
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 );

Open in new window

AleksAuthor Commented:
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

Open in new window


But It didn't filter anything out. I got ALL results.
bcnagelCommented:
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.
AleksAuthor Commented:
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

Open in new window

bcnagelCommented:
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

Open in new window

AleksAuthor Commented:
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.
bcnagelCommented:
The previous statement shouldn't filter results--it wasn't intended to. It's just to establish that the syntax works.

I don't think I can be of any additional help with the Msg 102 error. There is nothing that should stop a simple query like this from running in SQL 2008R2. Perhaps another expert will have an idea.

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

Open in new window


Maybe open another question, fix the syntax problem you are having, then come back to this question?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Thx. I'll wait for tomorrow then
AleksAuthor Commented:
For the time being I had to shelf this
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.