sql生成（安插、修改、删除数据的存储过程）代码的存储过程

GO/****** 对象:  StoredProcedure [dbo].[pro_GenerateProSet]    脚本日期: 08/03/2012 11:26:43 ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pro_GenerateProSet]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[pro_GenerateProSet]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* DECRIPTION: 生成（插入、修改、删除数据的存储过程）代码的存储过程** VERSION      AUTH          DATE          Defect No			DESC** --------  ------------  ------------  -----------------   ------------------------------** V000.0.1    pukuimin     08/03/2012							新建程序						** --------  ------------  ------------  -----------------   -------------------------------*******************************************************/create procedure [dbo].[pro_GenerateProSet](					@TbName NVARCHAR(200) ---表名)asbeginDECLARE @TableName     NVARCHAR(200)  --表名DECLARE @Parameters     VARCHAR(8000)  --所有参数DECLARE @UpdateFields     VARCHAR(8000) --更新参数（无主键和唯一键）DECLARE @InsertFields     VARCHAR(8000) --插入列（无唯一键）DECLARE @InsertParams     VARCHAR(8000) --插入参数（无唯一键）DECLARE @PrimaryKey     NVARCHAR(200)  --主键DECLARE @UniqueColumn     NVARCHAR(200)  --唯一键SELECT @TableName = @TbName,@Parameters = '', @UpdateFields = '',@InsertParams='',@InsertFields='',@UniqueColumn=''if isnull(@TableName,'')=''begin	print '表名不能为空！'	return 0end ----找表的主键SELECT @PrimaryKey =  [dbo].[fun_get_PrimaryKey](@TableName)if isnull(@PrimaryKey,'')=''begin	print '没有找到表主键！'	set @PrimaryKey=''	return 0end select @UniqueColumn = [dbo].[fun_get_unique_column](@TableName)----输出存储过程名PRINT 'CREATE PROCEDURE dbo.pro_set_'+dbo.fun_get_UpperFirst(@tablename)+'('PRINT dbo.fun_get_tabspace(1)[email protected]_typ int , ---- 1：新增 2: 修改 3：删除'----全部参数SELECT @Parameters = @Parameters + dbo.fun_get_tabspace(1)+'@'+COLUMN_NAME+' '+        DATA_TYPE+dbo.fun_get_column_length(@TableName,COLUMN_NAME)+','+CHAR(10)   ---- CHAR(10)就是一个换行符FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKeyset @Parameters=LEFT(@Parameters,LEN(@Parameters)-2) ----去掉最后一个 ‘,’ 和 换行符---插入参数SELECT @InsertParams = @InsertParams + dbo.fun_get_tabspace(3)+'@'+COLUMN_NAME+' '+','+CHAR(10) -- CHAR(10)为换行符FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @TableName AND COLUMN_NAME != @UniqueColumnset @InsertParams=LEFT(@InsertParams,LEN(@InsertParams)-2) ----去掉最后一个 ‘,’ 和 换行符set @InsertFields=Replace(@InsertParams,'@','')PRINT @ParametersPRINT ')'PRINT 'AS'PRINT 'BEGIN'	print dbo.fun_get_tabspace(1)+'if @opr_typ=1  ---新增'		print dbo.fun_get_tabspace(2)+'begin'		print dbo.fun_get_tabspace(2)+'INSERT INTO ' + @TableName+'('		print @InsertFields		print dbo.fun_get_tabspace(3)+')'		print dbo.fun_get_tabspace(2)+'VALUES('		print @InsertParams		print dbo.fun_get_tabspace(3)+')'		print dbo.fun_get_tabspace(3)+'select  SCOPE_IDENTITY() as autocolumn'		print dbo.fun_get_tabspace(2)+'end'	print dbo.fun_get_tabspace(1)+'else'	print dbo.fun_get_tabspace(1)+'if @opr_typ=2  ---修改'		print dbo.fun_get_tabspace(2)+'begin'		PRINT dbo.fun_get_tabspace(2)+'UPDATE ' + @TableName		PRINT dbo.fun_get_tabspace(2)+'SET'		SELECT @UpdateFields = @UpdateFields +dbo.fun_get_tabspace(3)+COLUMN_NAME +' = @'+ COLUMN_NAME +','+ CHAR(10)		FROM INFORMATION_SCHEMA.COLUMNS		WHERE TABLE_NAME = @TableName AND COLUMN_NAME != @PrimaryKey and COLUMN_NAME [email protected]		PRINT LEFT(@UpdateFields,LEN(@UpdateFields) -2)		PRINT dbo.fun_get_tabspace(2)+'WHERE'		PRINT dbo.fun_get_tabspace(3)+ @PrimaryKey +' = @' + @PrimaryKey		print dbo.fun_get_tabspace(1)+'end'	print dbo.fun_get_tabspace(1)+'else'	print dbo.fun_get_tabspace(1)+'if @opr_typ=3  ---根据主键删除'		print dbo.fun_get_tabspace(2)+'begin'print dbo.fun_get_tabspace(2)+'DELETE FROM ' + @TableNameprint dbo.fun_get_tabspace(3)+'where ' PRINT dbo.fun_get_tabspace(3)+ @PrimaryKey +' = @' + @PrimaryKey		print dbo.fun_get_tabspace(2)+'end'PRINT 'END'end/*exec pro_GenerateProSet 'stuinfo'select * from INFORMATION_SCHEMA.COLUMNS where table_name='stuinfo'fn_Trace_Gettable*/