CODEWITHSUNDEEP
t-sqlparametersssrs-2008reporting-services
dp3
dp3

Reputation: 1677

SSRS - Refine WHERE Criteria in T-SQL Query

I am developing a report that should return results depending on user-specified parameters. There are 3 report parameters, one is a drop down with 'Locations', and the other two are text parameters to search within 'user_id' and users' first or last name. This is what I am currently using:

SELECT * 
FROM UserTable1
WHERE Location = @Location
AND (user_id LIKE '%'+@SearchUserID+'%'
AND first_name LIKE '%'+@SearchUserName+'%' 
OR last_name LIKE '%'+@SearchUserName+'%')

If the @SearchUserID parameter field is left blank by the user, all user_ids should be returned (that meet the other 2 parameters). If specified, it will return user_ids that have that substring in the user_id. Same thing goes for the @SearchUserName parameter field. If left blank, it should return all users that meet the other parameters. If specified, it should return all users that have that substring in their first_name or last_name (but only results that meet the other parameters). If both are left blank, it should return all users.

The query I have above is not working. It seems to just return results meeting the @SearchUserName parameter, but disregards the @SearchUserID parameter. Is there a way to fix this? I had put the @SearchUserID filter on the tablix itself, using the expression: ="" & Parameters!SearchUserID.Value & "" But this has resulted in severely hampered performance..

Upvotes: 0

Views: 199

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this. You must pass NULL if parameter is blank(empty).

SELECT * 
FROM UserTable1
WHERE Location = @Location
AND (user_id LIKE '%'+@SearchUserID+'%'
       OR @SearchUserID IS NULL
    )
AND (first_name LIKE '%'+@SearchUserName+'%'
       OR last_name LIKE '%'+@SearchUserName+'%'
       OR @SearchUserName IS NULL
    )

"....but disregards the @SearchUserID parameter." What data type of user_id?

Upvotes: 2

Related Questions