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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 生成(插入、修改、删除数据的存储过程)代码的存储过程
** VERSION AUTH DATE Defect No DESC
** -------- ------------ ------------ ----------------- ------------------------------
** V000.0.1 pukuimin 08/03/2012 新建程序
** -------- ------------ ------------ ----------------- -------------------------------
*******************************************************/
create procedure [dbo].[pro_GenerateProSet](
@TbName NVARCHAR(200) ---表名
)
as
begin
DECLARE @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 0
end
----找表的主键
SELECT @PrimaryKey = [dbo].[fun_get_PrimaryKey](@TableName)
if isnull(@PrimaryKey,'')=''
begin
print '没有找到表主键!'
set @PrimaryKey=''
return 0
end
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)+'@opr_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.COLUMNS
WHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKey
set @Parameters=LEFT(@Parameters,LEN(@Parameters)-2) ----去掉最后一个 ‘,’ 和 换行符
---插入参数
SELECT @InsertParams = @InsertParams + dbo.fun_get_tabspace(3)+'@'+COLUMN_NAME+' '+','+CHAR(10) -- CHAR(10)为换行符
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME != @UniqueColumn
set @InsertParams=LEFT(@InsertParams,LEN(@InsertParams)-2) ----去掉最后一个 ‘,’ 和 换行符
set @InsertFields=Replace(@InsertParams,'@','')
PRINT @Parameters
PRINT ')'
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 !=@UniqueColumn
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 ' + @TableName
print 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
*/