具有多个可选参数的存储过程 [英] Stored Procedure with multiple optional parameters

问题描述

你好，



在我的存储过程中，用户可以只输入serviceId，有时候输入serviceid和personId，



我很累这样，但它不会来，请帮助我。

Hello,

In my stored procedure, user can give input as only serviceId and some time both serviceid and personId,

I tired like this, But it is not coming, Please help me.

WHERE StatusId != 3 AND

(@ServiceId IS NULL OR ServiceId=@ServiceId)

AND (PersonId IS NULL OR PersonId=@PersonId)

问候

Nagaraj.J

Regards
Nagaraj.J

推荐答案

尝试另一个atsign。

更改

Try another atsign.
Change

AND (PersonId IS NULL OR PersonId=@PersonId)

要

To

AND (@PersonId IS NULL OR PersonId=@PersonId)

尝试动态查询：

Try dynamic query:

DECLARE @sql VARCHAR(MAX)

SET @sql = 'SELECT <FieldList> FROM TableName WHERE StatusId !=3'

IF (@ServiceId IS NULL) 
    SET @sql = @sql + ' AND ServiceId=' + @ServiceId
 
IF (@PersonId IS NULL) 
    SET @sql = @sql + ' AND PersonId=' + @PersonId

EXEC(@sql)

有关更多信息，请参阅：

在存储过程中构建动态SQL [ ^ ]

如何生成T-SQL代码使用动态SQL？ [ ^ ]

T-SQL中具有多个条件的动态ORDER BY [ ^ ]

For furter information, please see:
Building Dynamic SQL In a Stored Procedure[^]
How to Generate T-SQL Code by Using Dynamic SQL?[^]
Dynamic ORDER BY with multiple criteria in T-SQL[^]

这篇关于具有多个可选参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！