troubleshooting Question

Help with stored procedure

Avatar of Aleks
AleksFlag for United States of America asked on
Web DevelopmentASPSQL
30 Comments1 Solution175 ViewsLast Modified:
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;

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

Should anything in the code be changed or the SP  so that it works with text values?
ASKER CERTIFIED SOLUTION
bcnagel

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 30 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 30 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros