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