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

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 

                              <p><i></i>3  <i></i></p>

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 

                              <p><i></i>3  <i></i></p>