存储过程动态SQL的方式

 

IF(OBJECT_ID('tempdb..#Books') IS NOT NULL)
BEGIN
 DROP TABLE #Books;
END

CREATE TABLE #Books
(
 BookName NVARCHAR(20),
 BookCode NVARCHAR(20)
)

INSERT INTO #Books( BookName, BookCode ) VALUES(N'C# 本质论','JC556874');
INSERT INTO #Books( BookName, BookCode ) VALUES(N'JS 本质论','JS556874');

第一种方式

DECLARE @paraBookName NVARCHAR(20);
SET @paraBookName='C';

DECLARE @Query NVARCHAR(2000);
SET @Query='';

SET @Query=@Query+'SELECT * FROM #Books WHERE 1=1 ';

IF(@paraBookName IS NOT NULL AND @paraBookName<>'')
BEGIN
 SET @Query=@Query+'AND BookName LIKE ''%'+@paraBookName+'%'' ';
END

EXEC(@Query);

第二种方式

DECLARE @paraBookName NVARCHAR(20);
SET @paraBookName='C';

SELECT * FROM #Books WHERE 1=1
AND (@paraBookName IS NULL OR @paraBookName='' OR BookName LIKE '%'+@paraBookName+'%')