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