带有可选参数的存储过程，但至少需要一个参数

Is it possible to create a stored procedure that has parameters which are all optional individually, but at least one of the parameters must be provided?

是否可以创建一个存储过程,其中的参数都是可选的,但必须至少提供一个参数?

For example, if I have a procedure that updates a record, I must pass in a record id, and then at least one column to update. The procedure should check to ensure at least one additional parameter/column is provided.

例如,如果我有一个更新记录的过程,我必须传入一个记录ID,然后至少一列要更新。该过程应检查以确保至少提供一个附加参数/列。

3 个解决方案

#1

I would do this in my programming language of choice instead of a stored proc. Why? This is the type of logic checking that TSQL is not very good at; the syntax for checking this will be "icky" and it will be slow.

我会用我选择的编程语言而不是存储过程来做到这一点。为什么?这是TSQL不太擅长的逻辑检查类型;检查这个的语法将是“icky”,它会很慢。

Also, the biggest performance advantage a stored proc gives you is running compiled SQL. In this case, because the SQL needs to be dynamically built, you lose that advantage. Thus, why do it as a stored procedure?

此外,存储过程为您提供的最大性能优势是运行已编译的SQL。在这种情况下,由于SQL需要动态构建,因此您将失去这一优势。那么,为什么它作为存储过程呢?

#2

I would put an if statement as the first action.

我会把if语句作为第一个动作。

IF @param1 is null and @param2 isnul and @param3 is null
Begin
--steps tpo raise an error or exit the proc
End

#3

Use the following in where clause

在where子句中使用以下内容

where
(isnull(@param1,0)=0 or id=@param1)
and 
(isnull(@param2,'')='' or name=@param2)

#1