[Original] Change SQL dynamic query to SQL query

/*
Author: A niu (NIU kunliang) QQ: 273352165 MSN: niukl@hotmail.com

Disclaimer: free of charge. Please keep this information
If you have any questions, please tell me!
*/
When we use stored procedures, we sometimes have to use dynamic queries to combine query conditions. For example, the following code:

Create   Proc Usp_search
@ City   Int
As
Begin
Declare   @ SQL   Varchar ( 8000 )
Set   @ SQL   = N ' Select * From testtable where 1 = 1 '
If ( @ City   <>   - 1 )
Set   @ SQL   =   @ SQL   +   ' And cityid = @ City '
Exec Sp_execute_ SQL @ SQL , N ' @ City int ' , @ City
End
Go

If you do not need dynamic SQL, you can change it to the following stored procedure:

Create   Proc Usp_search
@ City   Int
As
Begin
Select   *   From Testtable Where   1 = 1   And (@ City = -1   Or Cityid =   @ City )
End
Go