March 13, 2014 at 9:47 am
Hi
I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.
Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.
For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.
SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName
FROM Studies AS St
-- get activities
INNER JOIN Activities AS Ac ON (St.studyIncId = Ac.studyIncId AND St.studySqlId = Ac.studySqlId) AND Ac.isDeleted = 0x0
INNER JOIN Local_ActivitiesAudits AS LOA ON LOA.activityIncId = Ac.activityIncId and LOA.activitySqlId = Ac.activitySqlId AND LOA.isDeleted = 0x0
INNER JOIN Local_Audits AS LU ON LU.auditIncId = LOA.auditIncId and LU.auditSqlId = LOA.auditSqlId AND LU.isDeleted = 0x0
WHERE St.isDeleted=0x0
AND Ac.activityEndDate IS NOT NULL
--- -- for selecting multiple auditCodes -----------
AND charindex(LU.auditCode,@auditCode)<>0
March 13, 2014 at 9:56 am
You mean that the input parameter contains a string like: 'INPS45,INPS46,XYZK45'?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 13, 2014 at 2:32 pm
how about defining @auditCode in your variable options to be @auditcode = (select DISTINCT auditCode from Local_Audits)
and then just change your last line of code to be AND LU.auditCode in (@auditCode)
behind the scenes, SSRS will create a drop down box that users can check the Audit codes they want and the query will pass what they check into the IN statement.
March 14, 2014 at 5:50 am
You might use a simple user defined table type with one column; insert the multiple values you are using as the search criteria. Use the populated udt as an input parameter to your procedure and then instead of searching for a character set change to a where clause that looks in a table value parameter.
CREATE TYPE udt_AuditCode AS TABLE (auditCode varchar(xxx))
INSERT INTO udt_AuditCode SELECT AuditCode FROM ExternalSource
CREATE PROC usp_GetValues
(
@AuditCode udt_AuditCode
)
AS
SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName
FROM Studies AS St
-- get activities
INNER JOIN Activities AS Ac ON (St.studyIncId = Ac.studyIncId AND St.studySqlId = Ac.studySqlId) AND Ac.isDeleted = 0x0
INNER JOIN Local_ActivitiesAudits AS LOA ON LOA.activityIncId = Ac.activityIncId and LOA.activitySqlId = Ac.activitySqlId AND LOA.isDeleted = 0x0
INNER JOIN Local_Audits AS LU ON LU.auditIncId = LOA.auditIncId and LU.auditSqlId = LOA.auditSqlId AND LU.isDeleted = 0x0
WHERE St.isDeleted=0x0
AND Ac.activityEndDate IS NOT NULL
--- -- for selecting multiple auditCodes -----------
AND LU.auditCode in (SELECT AuditCode FROM @AuditCode)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply