sql生成（插入、修改、删除数据的存储过程）代码的存储过程(二)
com
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' www.2cto.com
end
/*
exec pro_GenerateProSet 'stuinfo'
select * from INFORMATION_SCHEMA.COLUMNS where table_name='stuinfo'
fn_Trace_Gettable
*/
作者 pukuimin1226