22,210
社区成员
发帖
与我相关
我的任务
分享
EXEC('select * from TableName '+@andWhere)
EXEC('select * from TableName '+@andWhere)
这句怎么理解?USE tempdb
GO
IF OBJECT_ID('P1'.'P') IS NOT NULL
DROP PROCEDURE P1
GO
CREATE PROCEDURE P1(
@Parameter1 NVARCHAR(50),
@Parameter2 NVARCHAR(50),
@Parameter3 NVARCHAR(50),
@Parameter4 NVARCHAR(50)
)
AS
DECLARE @andWhere NVARCHAR(2000)
SET @andWhere=' where 1=1 '
IF @Parameter1 IS NOT NULL
SET @andWhere=' and Col1='''+@Parameter1+''''
IF @Parameter2 IS NOT NULL
SET @andWhere=' and Col2='''+@Parameter2+''''
IF @Parameter3 IS NOT NULL
SET @andWhere=' and Col3='''+@Parameter3+''''
IF @Parameter4 IS NOT NULL
SET @andWhere=' and Col4='''+@Parameter4+''''
EXEC('select * from TableName '+@andWhere)
create proc pr_test
@p1 int = null
,@p2 int = null
,@p3 varchar(10) = null
,@p4 varchar(10) = null
as
select *
from test
where (@p1 is null or f1=@p1)
and (@p2 is null or f2=@p2)
and (@p3 is null or f3=@p3)
and (@p4 is null or f4=@p4)
go
--调用
exec pr_test 1,1,'2','2'
go
exec pr_test 1,1
go
exec pr_test @p1=1,@p3='2'
go