We help IT Professionals succeed at work.
Get Started

Help with stored procedure

Aleks
Aleks asked
on
174 Views
Last Modified: 2021-04-21
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?
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 30 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE