创建动态SQL存储过程的代码实例

创建动态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