Solved

Help with stored procedure

Posted on 2016-07-31
30
51 Views
Last Modified: 2016-08-14
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?
0
Comment
Question by:amucinobluedot
  • 16
  • 14
30 Comments
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736890
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?
0
 

Author Comment

by:amucinobluedot
ID: 41736892
I guess. How would I need to change the sp then ?
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736894
Best answer would be to change the database so that a.tags becomes a tags table. Can you do that?
0
 

Author Comment

by:amucinobluedot
ID: 41736896
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 ?
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736902
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

0
 

Author Comment

by:amucinobluedot
ID: 41736903
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 ?
0
 

Author Comment

by:amucinobluedot
ID: 41736904
Will that work with text ?
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736906
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?
0
 

Author Comment

by:amucinobluedot
ID: 41736918
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

0
 

Author Comment

by:amucinobluedot
ID: 41736919
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

0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736920
Looks good. The "ltrim(rtrim(" piece means you won't have to worry about extraneous spaces.
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736925
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

0
 

Author Comment

by:amucinobluedot
ID: 41736926
same error
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736927
Can you tell which lines are line 43 and line 44?
0
 

Author Comment

by:amucinobluedot
ID: 41736935
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 6

Expert Comment

by:bcnagel
ID: 41736937
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
0
 

Author Comment

by:amucinobluedot
ID: 41736939
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

0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736940
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

0
 

Author Comment

by:amucinobluedot
ID: 41736941
Now I get this:

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

Expert Comment

by:bcnagel
ID: 41736944
How about re-typing the code? As far as I can see, it should run clean on 2008R2.
0
 

Author Comment

by:amucinobluedot
ID: 41736947
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
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736950
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

0
 

Author Comment

by:amucinobluedot
ID: 41736951
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.
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736952
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.
0
 

Author Comment

by:amucinobluedot
ID: 41736953
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

0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41736959
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

0
 

Author Comment

by:amucinobluedot
ID: 41736964
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.
0
 
LVL 6

Accepted Solution

by:
bcnagel earned 500 total points
ID: 41736965
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?
0
 

Author Comment

by:amucinobluedot
ID: 41736968
Thx. I'll wait for tomorrow then
0
 

Author Closing Comment

by:amucinobluedot
ID: 41755412
For the time being I had to shelf this
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Read about why website design really matters in today's demanding market.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now