Safe and efficient comparison of SQL multi-conditional queries

ALTER PROCEDURE _tmp@id varchar (a), @PN varchar, @Type intasbegin/**********************************--Features: Multi-conditional query performance _tmp ' K3g8kg6nn94sbbs0 ', ' k7f7ff ', 0************************** ********/PRINT 'number of test data bars 500W' Set NOCOUNT ondeclare @time datetimedeclare @Warring VARCHAR (@Type =1 OR @Type =0) beginset @Warring =char (10) + 'the first way, the direct spelling of SQL statements, there is a SQL injection vulnerability‘; PRINT @Warring; Select @WarringSET @time =getdate () DECLARE @SQL VARCHAR (4000) Select @SQL = 'SELECT * from Dbo.tb_timetest WHERE 1=1' IF (ISNULL (@ID, '') <> '') SET @SQL [email protected]sql+ 'and id= ' '' [Email protected]+ '"'' IF (ISNULL (@PN, '') <> '') SET @SQL [email protected]sql+ 'and pn= ' '' [Email protected]+ '"'' EXEC (@SQL) PRINT 'time Required _ milliseconds' PRINT DATEDIFF (millisecond, @time, GETDATE ()) ENDIF (@Type =2 OR @Type =0) beginset @Warring =char (10) + 'The second way, there is no SQL vulnerability like the first, but the performance is significantly discounted, which is time consuming‘; PRINT @Warring; Select @WarringSET @time =getdate () SELECT * from Dbo.tb_timetest WHERE (ISNULL (@ID, '') = '' or id = @ID) and (PN = @PN OR @PN is NULL) PRINT 'time Required _ milliseconds' PRINT DATEDIFF (millisecond, @time, GETDATE ()) ENDIF (@Type =3 OR @Type =0) beginset @Warring =char (10) + 'The Third Way, although the wording does not have a second concise, but there is no such as the first way of SQL injection vulnerability, I can now think of the best‘; PRINT @Warring; SELECT @WarringSET @time =getdate () DECLARE @S NVARCHAR (4000), @P NVARCHAR (4000) SET @p=n '@ID varchar, @PN varchar' SET @s= 'SELECT * from Dbo.tb_timetest WHERE 1=1' IF (ISNULL (@ID, '') <> '') SET @[email protected]+ 'and id = @ID' IF (ISNULL (@ID, '') <> '') SET @[email protected]+ 'and PN = @PN' EXEC sp_executesql @s,@p,@[email protected],@[email protected]print 'time Required _ milliseconds' PRINT DATEDIFF (millisecond, @time, GETDATE ()) EndEnd

Safe and efficient comparison of SQL multi-conditional queries