sql生成（c#调用存储过程回来数据表）代码的存储过程

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 ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* 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) ---表名)asbeginDECLARE @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 0end  if exists (select * from sys.all_parameters where object_id = object_id(@ProName))   begin  	select 	@opr_typstr=case when [name][email protected]_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],'@','')), --参数	@[email protected]+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),	@[email protected]+dbo.fun_get_tabspace(3)+    (CASE     when  [name][email protected]_typ' then 'paras['+cast(@ParamCount as varchar(20))+'].Value = [email protected]+';'	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 = [email protected]+';'    ELSE		'if ([email protected]+'.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 = [email protected]+';'+		CHAR(10)+dbo.fun_get_tabspace(3)+'}'    END)+CHAR(10),	@[email protected]+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) endelse 	begin		print '没有此存储过程！'		return 0	endprint dbo.fun_get_tabspace(2)+'#region [email protected]+'的函数'print dbo.fun_get_tabspace(2)+'/// <summary>'print dbo.fun_get_tabspace(2)+'/// [email protected]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>'endprint dbo.fun_get_tabspace(2)+'/// <param name="model">[email protected]+'对应的model对象 </param>'print dbo.fun_get_tabspace(2)+'public object [email protected]+'([email protected][email protected]+'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 @SqlParamsprint dbo.fun_get_tabspace(3)+'};'print @ParamValueprint dbo.fun_get_tabspace(3)+'try'print dbo.fun_get_tabspace(3)+'{'print dbo.fun_get_tabspace(4)+'result = new DbHelper().ExecProDataTable("[email protected]+'", 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'*/