创建动态sql存储过程的代码实例
use [databasename] go set ansi_nulls on go set quoted_identifier on go -- ============================================= -- author: -- create date: -- description: -- ============================================= create procedure procname @p1 int = null, @p2 nvarchar(12) = null, as set nocount on; declare @sql nvarchar(4000) set @sql = 'select row_number() over(order by fieldname) as row ,fieldname_1 ,... ,fieldname_n from tablename where fieldname = value' +case when @p1 is null then '' else ' and fieldname_1 = @p1' end +case when @p2 is null then '' else ' and fieldname_2 = @p2' end exec sp_executesql @sql, n'@p1 int = null, @p2 nvarchar(12) = null,',@p1,@p1 go