如何修改ssms参数以包含全部排除？ [英] How do i modify ssms parameters to include an exclude all?

问题描述

我的SSMS存储过程中有一个团队组参数。团队组参数有4个值。到目前为止，这是我编码的：

I have a team group parameter in my SSMS stored procedure. The team group parameter has 4 values. So far this is what I've coded:

Declare
 @Param1 int = 1
,@Param2 varchar(4) = '1169'
,@TeamGroup varchar(150) = 'RCBE,RLEG,RPEN,RSPC'

-- Code below supports multi-select in report

if OBJECT_ID('Tempdb..#TeamGroups','U') is not null
   drop table #TeamGroups
CREATE TABLE #TeamGroups
(TeamGroupKey varchar(100))
INSERT INTO #TeamGroups
SELECT value FROM HEA.Parselist(@TeamGroup,',')

后来我将#Teamgroups表加入到我的主查询中。那么我的问题是：在创建Temp表时是否可以使用case语句，以便我可以包含select all或者排除所有值？或者我应该采用不同的方法吗？



我尝试过：



我已经研究了MSDN参数以及如何修改它们。

I later join my #Teamgroups table to my main query. My question then: is it possible to use a case statement when I create my Temp table, so that I can include a select all or exclude all value? Or should I approach this differently?

What I have tried:

I've researched MSDN parameters as well as how to modify them.

推荐答案

有很多方法可以做到这一点。但是，一种简单的方法是做这样的事情：

There are many ways to do it; however, one easy way would be to do something like this:

SELECT value FROM HEA.Parselist(@TeamGroup, ',')
UNION 
SELECT team
FROM TeamGroups
WHERE @TeamGroup = 'All'

如果@TeamGroup是全部的关键字，那将在你所有的团队中结合

That would union in all your teams if @TeamGroup was a keyword of "All"

这篇关于如何修改ssms参数以包含全部排除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！