USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE ProcName
@ID INT, -- 必须
@name NVARCHAR(50) = NULL,
@sex NVARCHAR(50) = NULL,
@UserName NVARCHAR(50), -- 必须
@IP NVARCHAR(50) -- 必须
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000)
DECLARE @RET1 NVARCHAR(10)
DECLARE @RET2 NVARCHAR(10)
-- 操作
SET @SQL = 'UPDATE DBO.tableName SET ShuXing = ShuXing' --一个无关紧要的字段,便于后面的拼接
+CASE WHEN @name IS NULL THEN '' ELSE ' ,[name] = @name' END
+CASE WHEN @sex IS NULL THEN '' ELSE ' ,sex = @sex' END
+' WHERE ID = @ID'
EXEC SP_EXECUTESQL @SQL, N'@ID INT,
@name NVARCHAR(50) = NULL
,@sex NVARCHAR(50) = NULL'
,@ID
,@name
,@sex
SET @RET1 = @@ROWCOUNT
-- 写日志
INSERT INTO DBO.tableName(field_1, ..., field_n)
VALUES(value_1, ..., value_n)
SET @RET2 = @@ROWCOUNT
-- 结果集
SELECT @RET1 AS alterRet, @RET2 AS logRet
GO