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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy