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