欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-08 12:24:33
创建动态sql存储过程的代码实例 use [databasename] go set ansi_nulls on go set quoted_identifier on go --...

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