创建动态SQL的存储过程

最新推荐文章于 2024-08-09 15:02:39 发布

多难成佛

最新推荐文章于 2024-08-09 15:02:39 发布

阅读量670

点赞数

分类专栏： SQLServer 文章标签： SQLServer

本文链接：https://blog.csdn.net/Adondon/article/details/80646175

版权

SQLServer 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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