[sql]
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'
*/
作者：pukuimin1226
