sql server自动生成增删改查的存储过程_sysgetobjectpkid-优快云博客

本文链接：https://blog.youkuaiyun.com/fanwenhu/article/details/125275536

这个博客内容涉及一个SQL存储过程的自动化创建，用于处理表的基础信息查询、数据的添加与修改以及删除操作。存储过程根据输入的表名和新过程名动态生成相应的SQL代码，包括错误处理和事务管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


/****** Object:  StoredProcedure [dbo].[Auto_CreateProcedure]    Script Date: 06/14/2022 11:14:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**  调用方式
EXEC [dbo].[Auto_CreateProcedure]
	 @TableName = N'FTP',	 --表名称
	 @ProcedureName = N'asdf'--新创建的存储过程名称
**/

ALTER PROCEDURE [dbo].[Auto_CreateProcedure]
@TableName VARCHAR(20),  --表名称
@ProcedureName VARCHAR(20) --新创建的存储过程名称
AS 
BEGIN
	SET NOCOUNT ON;
	  BEGIN TRY
	  	BEGIN TRAN
		--统计字段数量
		DECLARE @maxcol INT,@TableID INT 
		SET @TableID = object_id(@TableName)
		SELECT @MaxCol = max(colorder) FROM syscolumns WHERE id = @TableID

		CREATE TABLE #TempProc
		(
		 id INT IDENTITY(1,1) NOT NULL,
		 value VARCHAR(200) NULL
		)

		INSERT #TempProc
		SELECT 'Create Procedure ' + rtrim(@ProcedureName)+' '
		INSERT #TempProc
		SELECT convert(VARCHAR(35),'@' + syscolumns.name)+' '+ rtrim(systypes.name)
		+ CASE WHEN rtrim(systypes.name) IN ('binary','char','nchar','nvarchar','varbinary','VARCHAR') THEN '(' + rtrim(convert(char(4),syscolumns.length)) + ')'
		WHEN rtrim(systypes.name) NOT IN ('binary','char','nchar','nvarchar','varbinary','VARCHAR') THEN ' '
		END +' ='''','
		FROM syscolumns
		join systypes on syscolumns.xtype = systypes.xtype
		WHERE id = @TableID and systypes.name <> 'sysname'
		INSERT #TempProc (value)
		VALUES  ('@flag CHAR(1)='''',')
		INSERT #TempProc (value)
		VALUES  ('@MSG  NVARCHAR(200)='''' OUTPUT  ')
		INSERT #TempProc (value)
		VALUES  ('AS ')
		INSERT #TempProc (value)
		VALUES  ('BEGIN ')
		INSERT #TempProc (value)
		VALUES  (' SET NOCOUNT ON;')
		INSERT #TempProc (value)
		VALUES  (' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;')
		INSERT #TempProc (value)
		VALUES  (' --基础信息查询')
		INSERT #TempProc (value)
		VALUES  (' IF @flag=''0''')
		INSERT #TempProc (value)
		VALUES  (' BEGIN')

		SELECT id,name INTO #temp FROM syscolumns WHERE id= object_id(@TableName)
		DECLARE @fidtxt VARCHAR(2000),@id VARCHAR(20),@name VARCHAR(20)
		SELECT @fidtxt=stuff
		  (
			(SELECT ','+name FROM #temp a WHERE a.id=b.id FOR  XML PATH('')),1,1,''
		  ) 
		FROM #temp b GROUP BY  id
		DROP TABLE  #temp
		INSERT #TempProc (value)
		VALUES  ('	SELECT '+@fidtxt+' FROM '+@TableName)
		INSERT #TempProc (value)
		VALUES  ('	SET @MSG =''OK,基础信息加载完毕!''') 
		INSERT #TempProc (value)
		VALUES  ('	RETURN 0')
		INSERT #TempProc (value)
		VALUES  (' END')
		INSERT #TempProc (value)
		VALUES  (' --数据的添加与修改')
		INSERT #TempProc (value)
		VALUES  (' IF @flag=''1''')
		INSERT #TempProc (value)
		VALUES  (' BEGIN')
		SELECT @id=name FROM syscolumns WHERE id= object_id(@TableName)and colid=1
		INSERT #TempProc (value)
		VALUES  ('   IF ISNULL(@'+@id+','''')=''''')
		INSERT #TempProc (value)
		VALUES  ('   BEGIN')
		SELECT @name=name FROM syscolumns WHERE id= object_id(@TableName) and colid=2
		INSERT #TempProc (value)
		VALUES  ('	 IF EXISTS(SELECT 1 FROM dbo.'+@TableName+' WHERE '+@name+'=@'+@name+')')
		INSERT #TempProc (value)
		VALUES  ('	 BEGIN')
		INSERT #TempProc (value)
		VALUES  ('	   SET @MSG =''NG;该信息在系统中已存在,信息重复,请确认!''') 
		INSERT #TempProc (value)
		VALUES  ('	   RETURN -1')
		INSERT #TempProc (value)
		VALUES  ('	 END')
		INSERT #TempProc (value)
		VALUES  ('	 EXEC [dbo].[SysGetObjectPKId] @ObjectName = '+@TableName+',@PKID = @'+@id+' OUTPUT')

		CREATE TABLE #TempTab
		(
		 value VARCHAR(200) NULL,
		 Num INT
		)
		INSERT #TempProc (value)
		SELECT '	 INSERT INTO dbo.' + @TableName
		INSERT #TempTab(value,Num)
		SELECT '	 (',@maxcol + 3 AS colorder
		UNION
		SELECT syscolumns.name
		+ CASE WHEN colorder < @maxcol THEN ','
		WHEN colorder = @maxcol THEN ' '
		END
		AS col,
		colorder + @maxcol + 3 AS colorder
		FROM syscolumns
		join systypes on syscolumns.xtype = systypes.xtype
		WHERE id = @TableID and systypes.name <> 'sysname'
		UNION
		SELECT ')',(2 * @maxcol) + 4 AS colorder
		--拼接字符串
		INSERT #TempProc(value)
		SELECT stuff((SELECT ''+value FROM #TempTab ORDER BY Num FOR XML PATH ('')),1,0,'') 
		TRUNCATE TABLE #TempTab
		
		INSERT #TempTab(value,Num)
		SELECT '	 VALUES',(2 * @maxcol) + 5 AS colorder
		UNION
		SELECT '	 (',(2 * @maxcol) + 6 AS colorder
		UNION
		SELECT '@' + syscolumns.name
		+ CASE WHEN colorder < @maxcol THEN ','
		WHEN colorder = @maxcol THEN ' '
		END
		AS col,
		colorder + (2 * @maxcol + 6) AS colorder
		FROM syscolumns
		join systypes on syscolumns.xtype = systypes.xtype
		WHERE id = @TableID and systypes.name <> 'sysname'
		UNION
		SELECT ')',(3 * @maxcol) + 7 AS colorder
		--拼接字符串
		INSERT #TempProc
		SELECT stuff((SELECT ''+value FROM #TempTab ORDER BY Num FOR XML PATH ('')),1,0,'') 

		TRUNCATE TABLE #TempTab
		INSERT #TempProc (value)
		VALUES  ('  	 SET @MSG=''OK,信息添加完毕!''') 
		INSERT #TempProc (value)
		VALUES  ('  	 RETURN 0')
		INSERT #TempProc (value)
		VALUES  ('   END')
		INSERT #TempProc (value)
		VALUES  ('   ELSE')
		INSERT #TempProc (value)
		VALUES  ('   BEGIN')
		INSERT #TempProc (value)
		VALUES  ('	 IF EXISTS(SELECT 1 FROM dbo.'+@TableName+' WHERE '+@name+'=@'+@name+' AND '+@id+'!=@'+@id+')')
		INSERT #TempProc (value)
		VALUES  ('	 BEGIN')
		INSERT #TempProc (value)
		VALUES  ('	   SET @MSG =''NG;该信息在系统中已存在,信息重复,请确认!''') 
		INSERT #TempProc (value)
		VALUES  ('	   RETURN -1')
		INSERT #TempProc (value)
		VALUES  ('	 END')

		INSERT #TempTab(value,Num)
		SELECT syscolumns.name+'=@'+syscolumns.name
		+ CASE WHEN colorder < @maxcol THEN ','
		WHEN colorder = @maxcol THEN ' '
		END
		AS col,
		colorder + @maxcol + 3 AS colorder
		FROM syscolumns
		join systypes on syscolumns.xtype = systypes.xtype
		WHERE id = @TableID and systypes.name <> 'sysname'
		--拼接字符串
		INSERT #TempProc
		SELECT ' 	 UPDATE dbo.'+@TableName+' SET '
		+STUFF((SELECT ''+value FROM #TempTab FOR XML PATH ('')),1,0,'')
		
		INSERT #TempProc(value)
		VALUES  (' 	 WHERE '+@id+'=@'+@id) 
		INSERT #TempProc (value)
		VALUES  (' 	 SET @MSG=''OK,信息更新完毕!''') 
		INSERT #TempProc (value)
		VALUES  (' 	 RETURN -1')
		INSERT #TempProc (value)
		VALUES  ('   END')
		INSERT #TempProc (value)
		VALUES  (' END')
		INSERT #TempProc (value)
		VALUES  (' --数据的删除')
		INSERT #TempProc (value)
		VALUES  (' IF @flag=''2''')
		INSERT #TempProc (value)
		VALUES  (' BEGIN')
		INSERT #TempProc (value)
		VALUES  ('   DELETE dbo.'+@TableName+' WHERE '+@id+'=@'+@id)
		INSERT #TempProc (value)
		VALUES  ('   SET @MSG=''OK;删除成功''')
		INSERT #TempProc (value)
		VALUES  ('   RETURN 0')
		INSERT #TempProc (value)
		VALUES  (' END')
		INSERT #TempProc (value)
		VALUES  ('END')
		DECLARE @sql NVARCHAR(max)

		DECLARE @maxrow INT,@T_id INT =1,@ms NVARCHAR(1000)=''
		SELECT TOP 1 @maxrow=id FROM #TempProc ORDER BY id DESC
		WHILE @T_id<=@maxrow
		BEGIN
		 SELECT @ms=value FROM #TempProc WHERE id=@T_id
		 SET @sql=ISNULL(@sql,'')+ISNULL(@ms,'')+Char(13)
		 SET @T_id=@T_id+1
		END
		PRINT @sql
		EXEC sp_executesql @sql
		DROP TABLE #TempTab
		DROP TABLE #tempproc
		COMMIT TRAN
		SELECT 'OK,存储过程生成成功!'
		RETURN 0
	END TRY 
	BEGIN CATCH
		ROLLBACK TRAN
		SELECT 'NG' AS '异常', ERROR_LINE() as Line,
          ERROR_MESSAGE() as message1,
          ERROR_NUMBER() as number,
          ERROR_PROCEDURE() as proc1,
          ERROR_SEVERITY() as severity,
          ERROR_STATE() as state1
          RETURN -1
	END CATCH		
END