GO
/****** 对象: StoredProcedure [dbo].[pro_GenerateServiceFunction] 脚本日期: 08/04/2012 11:26:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pro_GenerateServiceFunction]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pro_GenerateServiceFunction]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 生成(c#调用存储过程返回数据表)代码的存储过程
** VERSION AUTH DATE Defect No DESC
** -------- ------------ ------------ ----------------- ------------------------------
** V000.0.1 pukuimin 08/04/2012 新建程序
** -------- ------------ ------------ ----------------- -------------------------------
*******************************************************/
CREATE procedure [dbo].[pro_GenerateServiceFunction](
@ProName NVARCHAR(200), ---存储过程名
@TableName NVARCHAR(200) ---表名
)
as
begin
DECLARE @SqlParams VARCHAR(8000) --生成存储过程参数
DECLARE @ParamValue VARCHAR(8000) --参数赋值
declare @tempProperty varchar(200)--临时字段
declare @DATA_TYPE varchar(200)--临时数据类型
declare @ParamCount int --参数计数器
declare @opr_typstr varchar(20) --
SELECT @SqlParams='',@tempProperty='',@DATA_TYPE='',@ParamCount=0,@ParamValue='',@opr_typstr=''
if isnull(@ProName,'')='' or isnull(@TableName,'')=''
begin
print '存储过程名或表名不能为空!'
return 0
end
if exists (select * from sys.all_parameters where object_id = object_id(@ProName))
begin
select
@opr_typstr=case when [name]='@opr_typ' and @opr_typstr='' then 'int Opr_typ ,' else @opr_typstr end,
@DATA_TYPE=type_name(user_type_id), --sql类型
@tempProperty=dbo.fun_get_UpperFirst(replace([name],'@','')), --参数
@SqlParams=@SqlParams+dbo.fun_get_tabspace(3)+'new SqlParameter("'+[name]+'",'+
(CASE
WHEN @DATA_TYPE='NVARCHAR' OR @DATA_TYPE='VARCHAR' OR @DATA_TYPE='CHAR'OR @DATA_TYPE='NCHAR' or @DATA_TYPE='numeric'
THEN dbo.[fun_get_cssqlpdt_by_sqldt](@DATA_TYPE)+',' +dbo.[fun_get_param_length](@ProName,[name])
ELSE
dbo.[fun_get_cssqlpdt_by_sqldt](@DATA_TYPE)
END)+'),'+CHAR(10),
@ParamValue=@ParamValue+dbo.fun_get_tabspace(3)+
(CASE
when [name]='@opr_typ' then 'paras['+cast(@ParamCount as varchar(20))+'].Value = '+@tempProperty+';'
WHEN @DATA_TYPE='NVARCHAR' OR @DATA_TYPE='VARCHAR' OR @DATA_TYPE='CHAR' OR @DATA_TYPE='NCHAR' OR @DATA_TYPE='NTEXT' OR @DATA_TYPE='TEXT' OR @DATA_TYPE='OUT' or @DATA_TYPE='uniqueidentifier' or @DATA_TYPE='image' or @DATA_TYPE='variant'
THEN 'paras['+cast(@ParamCount as varchar(20))+'].Value = model.'+@tempProperty+';'
ELSE
'if (model.'+@tempProperty+'.Equals('+dbo.[fun_get_cssdt_by_sqldt](@DATA_TYPE)+'.MinValue))'+
CHAR(10)+dbo.fun_get_tabspace(3)+'{'+
CHAR(10)+dbo.fun_get_tabspace(4)+'paras['+cast(@ParamCount as varchar(20))+'].Value = null;'+
CHAR(10)+dbo.fun_get_tabspace(3)+'}'+
CHAR(10)+dbo.fun_get_tabspace(3)+'else'+
CHAR(10)+dbo.fun_get_tabspace(3)+'{'+
CHAR(10)+dbo.fun_get_tabspace(4)+'paras['+cast(@ParamCount as varchar(20))+'].Value = model.'+@tempProperty+';'+
CHAR(10)+dbo.fun_get_tabspace(3)+'}'
END)+CHAR(10),
@ParamCount=@ParamCount+1
from sys.all_parameters where object_id = object_id(@ProName)
set @SqlParams=LEFT(@SqlParams,LEN(@SqlParams)-2)
set @ParamValue=LEFT(@ParamValue,LEN(@ParamValue)-1)
end
else
begin
print '没有此存储过程!'
return 0
end
print dbo.fun_get_tabspace(2)+'#region 执行存储过程'+@ProName+'的函数'
print dbo.fun_get_tabspace(2)+'/// <summary>'
print dbo.fun_get_tabspace(2)+'/// 执行存储过程'+@ProName
print dbo.fun_get_tabspace(2)+'/// <summary>'
if @opr_typstr <>''
begin
print dbo.fun_get_tabspace(2)+'/// <param name="Opr_typ"> 操作类型,1:新增 2:修改,3 删除 </param>'
end
print dbo.fun_get_tabspace(2)+'/// <param name="model">'+@TableName+'对应的model对象 </param>'
print dbo.fun_get_tabspace(2)+'public object Run'+@ProName+'('+@opr_typstr+@TableName+'Model model)' --
print dbo.fun_get_tabspace(2)+'{'
print dbo.fun_get_tabspace(3)+'object result=new object();'
print dbo.fun_get_tabspace(3)+'SqlParameter[] paras = new SqlParameter[]'
print dbo.fun_get_tabspace(3)+'{'
print @SqlParams
print dbo.fun_get_tabspace(3)+'};'
print @ParamValue
print dbo.fun_get_tabspace(3)+'try'
print dbo.fun_get_tabspace(3)+'{'
print dbo.fun_get_tabspace(4)+'result = new DbHelper().ExecProDataTable("'+@ProName+'", paras);'
print dbo.fun_get_tabspace(4)+''
print dbo.fun_get_tabspace(3)+'}'
print dbo.fun_get_tabspace(3)+'catch (SqlException ex)'
print dbo.fun_get_tabspace(3)+'{'
print dbo.fun_get_tabspace(4)+'result = null;'
print dbo.fun_get_tabspace(4)+'throw new Exception("数据库操作异常", ex);'
print dbo.fun_get_tabspace(3)+'}'
print dbo.fun_get_tabspace(3)+'return result;'
print dbo.fun_get_tabspace(2)+'}'
print dbo.fun_get_tabspace(2)+'#endregion'
end
/*
exec [pro_GenerateServiceFunction] 'pro_get_Stuinfo','stuinfo'
*/