22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(c1 INT,c2 INT,c3 INT)
GO
INSERT INTO t VALUES (1,2,3)
INSERT INTO t VALUES (1,2,4)
INSERT INTO t VALUES (1,3,5)
INSERT INTO t VALUES (2,4,3)
GO
IF EXISTS(SELECT * FROM sys.procedures AS p WHERE p.name='ProcTest')
BEGIN
DROP PROC ProcTest
END
GO
CREATE PROC dbo.ProcTest
@p1 INT,
@p2 INT,
@p3 INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX),@sqlWhere NVARCHAR(MAX),@sqlGroup NVARCHAR(MAX)
SET @sql=''
SET @sqlWhere=' where 1=1 '
SET @sqlGroup=''
IF @p1 IS NOT NULL
BEGIN
SET @sql=@sql+',c1'
SET @sqlWhere=@sqlWhere+ ' and c1=@p1 '
SET @sqlGroup=CASE WHEN @sqlGroup>'' THEN ' ,c1 ' ELSE ' GROUP BY c1 ' END
END
IF @p2 IS NOT NULL
BEGIN
SET @sql=@sql+',c2'
SET @sqlWhere=@sqlWhere+' and c2=@p2 '
SET @sqlGroup= @sqlGroup+ CASE WHEN @sqlGroup>'' THEN ' ,c2 ' ELSE ' GROUP BY c2 ' END
END
IF @p3 IS NOT NULL
BEGIN
SET @sql=@sql+',c3'
SET @sqlWhere=@sqlWhere+' and c3=@p2 '
SET @sqlGroup=@sqlGroup+ CASE WHEN @sqlGroup>'' THEN ' ,c3 ' ELSE ' GROUP BY c3 ' END
END
SET @sql='select '+SUBSTRING(@sql,2,LEN(@sql))+',count(1) as cnt from t '+@sqlWhere+@sqlGroup
PRINT @sql
EXEC sp_executesql @sql,N'@p1 int,@p2 int,@p3 int',@p1,@p2,@p3
END
GO
EXEC dbo.ProcTest 1,3,NULL
你的需求这么复杂, 只能用动态SQL 来实现。
如果实在不用也行,那就得把每种组合情况都写下来, 代码都是这个的好多倍了, 不值。