如何实现两个参数，每个参数都是可选的，每个参数的可用值？

Two parameters that allow user to choose either one to sort data.

两个参数,允许用户选择其中一个来排序数据。

SELECT comment, comment_type,user
FROM comments
WHERE (comments.comment_type = @ctype) OR (comments.user = @user)

The user must be able to select each commenttype or user from available values dropdown. The problem is ssrs will not let me leave one blank. I tried using IN but it errored out.

用户必须能够从可用值下拉列表中选择每个评论类型或用户。问题是ssrs不会让我留下一个空白。我尝试使用IN但它出错了。

3 个解决方案

#1

How about

(comments.comment_type = @ctype AND comments.user IS NOT NULL) OR 
(comments.comment_type IS NOT NULL AND comments.user = @user)

#2

You should check Allow null value in Report parameters window.

您应该在“报告参数”窗口中选中“允许空值”。

Than you can try your query or below query.

您可以尝试查询或以下查询。

SELECT comment, comment_type,user
FROM comments
WHERE (comments.comment_type = coalesce(@ctype,comments.comment_type)) OR (comments.user = coalesce(@user,comments.user))

More information

#3

Try this:

CREATE PROC myproc (
@ctype varchar(10) = null,
@user varchar(10) = null
) as 
BEGIN

  SELECT comment, comment_type,user
  FROM comments
  WHERE (comments.comment_type = ISNULL(@ctype, comments.comment_type))
  OR (comments.user = ISNULL(@user,comments.user))

END

#1