生成（c#检查输入数据、web界面控件）代码的存储过程

USE [test]
GO
/****** 对象:  StoredProcedure [dbo].[pro_GenerateCheckInput_webControl]    脚本日期: 08/13/2012 10:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 生成（c#检查输入数据、web界面控件）代码的存储过程
** 大于两个字符，并以cd或id结尾的，用Combobox控件
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
ALTER procedure [dbo].[pro_GenerateCheckInput_webControl](
					@ProName NVARCHAR(200), ---存储过程名
					@TableName NVARCHAR(200) ---表名
)
--WITH ENCRYPTION ---加锁
as
begin
declare @tempProperty    varchar(200)--临时字段
declare @DATA_TYPE    varchar(200)--临时数据类型
declare @ckinput varchar(8000)  ----输入检查
declare @tempValue varchar(200)  --从控件取值
declare @webControls varchar(8000) ----web页面控件代码
declare @tempcomment varchar(100)
SELECT @tempProperty='',@DATA_TYPE='',@ckinput='',@tempValue='',@webControls='',@tempcomment=''

if isnull(@ProName,'')='' or isnull(@TableName,'')=''
begin
	print '存储过程名或表名不能为空！'
	return 0
end
set @webControls=@webControls+'<table cellSpacing="0" cellPadding="0" width="100%" border="0">'+CHAR(10)

 if exists (select * from sys.all_parameters where object_id = object_id(@ProName))
 begin
	select
	@DATA_TYPE=type_name(user_type_id),  --sql类型
	@tempProperty=dbo.fun_get_UpperFirst(replace([name],'@','')), --参数
	@tempcomment=dbo.fun_get_comment(@TableName,@tempProperty),
	@tempValue=(
				case
				when Right([name],2)='id' or Right([name],2)='cd'
				then 'this.DropDownList'+@tempProperty+'.SelectedValue.Trim()'
				else
				'this.TextBox'+@tempProperty+'.Text.Trim()'
				end
				),
	@ckinput=@ckinput+
    (CASE
    when  [name]='@opr_typ' or [name]='@ret' then ''
	WHEN @DATA_TYPE='NVARCHAR' OR @DATA_TYPE='VARCHAR' OR @DATA_TYPE='CHAR' OR @DATA_TYPE='NCHAR' OR @DATA_TYPE='NTEXT' OR @DATA_TYPE='TEXT'
    THEN dbo.fun_get_tabspace(3)+'if('+@tempValue+'.Length==0)'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'{'+CHAR(10)+
		dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'不能为空！\\n";'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'}'+CHAR(10)
	WHEN @DATA_TYPE='BIGINT'
	THEN dbo.fun_get_tabspace(3)+'if (!DataValidate.IsNumber('+@tempValue+'))'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'{'+CHAR(10)+
		dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'格式不正确！\\n";'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'}'+CHAR(10)
	WHEN @DATA_TYPE='INT'
	THEN dbo.fun_get_tabspace(3)+'if (!DataValidate.IsInt('+@tempValue+'))'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'{'+CHAR(10)+
		dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'格式不正确！\\n";'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'}'+CHAR(10)
	WHEN @DATA_TYPE='NUMERIC'
	THEN dbo.fun_get_tabspace(3)+'if (!DataValidate.IsDecimalSign('+@tempValue+'))'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'{'+CHAR(10)+
		dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'格式不正确！\\n";'+CHAR(10)+
		dbo.fun_get_tabspace(3)+'}'+CHAR(10)
    ELSE
		''
    END),  ------dbo.[fun_get_cssdt_by_sqldt](@DATA_TYPE)+'.MinValue)
	@webControls=@webControls+
(case
when  [name]='@opr_typ' or [name]='@ret' then ''
else
dbo.fun_get_tabspace(1)+'<tr>'+
char(10)+dbo.fun_get_tabspace(1)+ '<td height="25" width="30%" align="right">'+@tempcomment+'：</td>'+
char(10)+dbo.fun_get_tabspace(1)+'<td height="25" width="*" align="left">'+(
				case
				when Right([name],2)='id' or Right([name],2)='cd'
				then char(10)+dbo.fun_get_tabspace(1)+'<asp:DropDownList ID="DropDownList'+@tempProperty+'" runat="server" Width="120px"></asp:DropDownList>'
				else char(10)+dbo.fun_get_tabspace(1)+'<asp:TextBox id="TextBox'+@tempProperty+'" runat="server" Width="120px"></asp:TextBox>'
				end
				)+
char(10)+dbo.fun_get_tabspace(1)+'</td>'+char(10)+dbo.fun_get_tabspace(1)+'</tr>'+char(10)
end)
	from sys.all_parameters where object_id = object_id(@ProName)

 end
else
	begin
		print '没有此存储过程！'
		return 0
	end
set @webControls=@webControls+'</table>'

print dbo.fun_get_tabspace(3)+'#region 检查输入数据正确性'
print dbo.fun_get_tabspace(3)+'string strErr="";'
print @ckinput
print dbo.fun_get_tabspace(3)+'#endregion'
print char(10)+char(10)+char(10)
print '<!--输入控件 -->'
print @webControls
end
/*

exec [pro_GenerateCheckInput_webControl] 'pro_set_Stuinfo','stuinfo'

*/