CREATE
PROC
#AutoGeneration_Load_P
@TABLENAME VARCHAR ( 50 )
AS
BEGIN
DECLARE @HOST_NAME VARCHAR ( 200 )
DECLARE @GET_DATE DATETIME
DECLARE @SQLROC VARCHAR ( 4000 )
DECLARE @REMARK VARCHAR ( 2000 )
DECLARE @SQL VARCHAR ( 2000 )
DECLARE @PARAMETER VARCHAR ( 2000 )
DECLARE @DESCRIPTION VARCHAR ( 8000 )
DECLARE @WHERE_SQL VARCHAR ( 2000 )
DECLARE @ROWCOUNT INT
SELECT @SQLROC = '' , @DESCRIPTION = '' , @PARAMETER = '' , @REMARK = '' , @WHERE_SQL = '' , @SQL = '' ,
@HOST_NAME = HOST_NAME (), @GET_DATE = GETDATE ()
SET @SQLROC = @SQLROC + ' IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( '' SP_ ' + @TABLENAME + ' _Load '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
SET @SQLROC = @SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _Load ' + CHAR ( 10 )
SET @SQLROC = @SQLROC + ' GO '
SET NOCOUNT ON
CREATE TABLE #(TABLE_QUALIFIER VARCHAR ( 100 ),
TABLE_OWNER VARCHAR ( 100 ),
TABLE_NAME VARCHAR ( 100 ),
COLUMN_NAME VARCHAR ( 100 ),
KEY_SEQ VARCHAR ( 50 ),
PK_NAME VARCHAR ( 100 ))
INSERT INTO # EXEC SP_PKEYS @TABLENAME
SELECT @REMARK = @REMARK + ' ,@ ' + COLUMN_NAME,
@WHERE_SQL = @WHERE_SQL + ' AND ' + COLUMN_NAME + ' =@ ' + COLUMN_NAME + ''
FROM #
SELECT @PARAMETER = @PARAMETER + SPACE ( 4 ) + ' @ ' + LTRIM (NAME) + SPACE ( 20 - LEN (NAME)) +
CASE WHEN xtype = 34 THEN ' image '
WHEN xtype = 35 THEN ' text '
WHEN xtype = 36 THEN ' uniqueidentifier '
WHEN xtype = 48 THEN ' tinyint '
WHEN xtype = 52 THEN ' smallint '
WHEN xtype = 56 THEN ' int '
WHEN xtype = 58 THEN ' smalldatetime '
WHEN xtype = 59 THEN ' real '
WHEN xtype = 60 THEN ' money '
WHEN xtype = 61 THEN ' datetime '
WHEN xtype = 62 THEN ' float '
WHEN xtype = 98 THEN ' sql_variant '
WHEN xtype = 99 THEN ' ntext '
WHEN xtype = 104 THEN ' bit '
WHEN xtype = 106 THEN ' decimal '
WHEN xtype = 108 THEN ' numeric '
WHEN xtype = 122 THEN ' smallmoney '
WHEN xtype = 127 THEN ' bigint '
WHEN xtype = 165 THEN ' varbinary '
WHEN xtype = 167 THEN ' varchar ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 173 THEN ' binary '
WHEN xtype = 175 THEN ' char ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 189 THEN ' timestamp '
WHEN xtype = 231 THEN ' nvarchar ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 239 THEN ' nchar ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 241 THEN ' xml '
WHEN xtype = 231 THEN ' sysname ' END + ' , ' + CHAR ( 10 )
FROM SYSCOLUMNS A
WHERE ID = OBJECT_ID ( '' + @TABLENAME + '' )
AND NAME IN ( SELECT COLUMN_NAME FROM #)
DROP TABLE #
SET NOCOUNT OFF
SET @DESCRIPTION = @DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 过程名称:SP_ ' + @TABLENAME + ' _Load ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 功能说明:查询表 ' + @TABLENAME + ' 记录集合的存储过程 ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 入口参数: ' + STUFF ( @REMARK , 1 , 1 , '' ) + '' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 过程返回:返回所有记录集 ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 维护记录:Y/A ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 使用案例:SP_ ' + @TABLENAME + ' _Load ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 工作站名: ' + @HOST_NAME + '' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 联系方式:zlp321001@hotmail.com ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 创建日期: ' + CONVERT ( VARCHAR ( 20 ), @GET_DATE , 120 ) + '' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' +--------------------------------------+*/ ' + CHAR ( 10 )
SELECT @SQLROC = @SQLROC + CHAR ( 10 ) + @DESCRIPTION + ' CREATE PROC SP_ ' + @TABLENAME + ' _Load '
SET @SQLROC = @SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + LEFT ( @PARAMETER , LEN ( @PARAMETER ) - 2 ) + CHAR ( 10 )
SET @SQLROC = @SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
SET @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON '
SET @SQLROC = @SQLROC + CHAR ( 32 ) + @SQL
SET @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' SELECT * FROM ' + @TABLENAME + ' WHERE ' + STUFF ( @WHERE_SQL , 1 , 4 , '' ) + CHAR ( 10 )
SET @SQLROC = @SQLROC + SPACE ( 4 ) + ' SET NOCOUNT OFF '
SET @SQLROC = @SQLROC + CHAR ( 10 ) + ' END '
PRINT @SQLROC + CHAR ( 10 ) + ' GO '
END
GO
CREATE PROC #SP_Generation_Load
@TABLENAMES VARCHAR ( 8000 )
AS
BEGIN
DECLARE @I INT
DECLARE @TABLENAME VARCHAR ( 100 )
SET @I = CHARINDEX ( ' , ' , @TABLENAMES )
WHILE @I > 0
BEGIN
SET @TABLENAME = LEFT ( @TABLENAMES , @I - 1 )
EXEC #AutoGeneration_Load_P @TABLENAME
SET @TABLENAMES = RIGHT ( @TABLENAMES , LEN ( @TABLENAMES ) - @I )
SET @I = CHARINDEX ( ' , ' , @TABLENAMES )
END
IF LEN ( @TABLENAMES ) > 0
BEGIN
EXEC #AutoGeneration_Load_P @TABLENAMES
END
END
GO
-- 测试
#SP_Generation_Load ' t '
drop proc #AutoGeneration_Load_P
drop proc #SP_Generation_Load
-- 测试结果:
/**/ /*
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Load') AND XTYPE IN (N'P'))
DROP PROC SP_t_Load
GO
/*+--------------------------------------+
| 过程名称:SP_t_Load
| 功能说明:查询表t记录集合的存储过程
| 入口参数:@type
| 过程返回:返回所有记录集
| 维护记录:Y/A
| 使用案例:SP_t_Load
| 工作站名:RICHWAY-ZJ
| 联系方式:zlp321001@hotmail.com
| 创建日期:2006-08-31 12:26:37
+--------------------------------------+*/
CREATE PROC SP_t_Load
@type varchar ( 10 )
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM t WHERE type = @type
SET NOCOUNT OFF
END
GO
*/
@TABLENAME VARCHAR ( 50 )
AS
BEGIN
DECLARE @HOST_NAME VARCHAR ( 200 )
DECLARE @GET_DATE DATETIME
DECLARE @SQLROC VARCHAR ( 4000 )
DECLARE @REMARK VARCHAR ( 2000 )
DECLARE @SQL VARCHAR ( 2000 )
DECLARE @PARAMETER VARCHAR ( 2000 )
DECLARE @DESCRIPTION VARCHAR ( 8000 )
DECLARE @WHERE_SQL VARCHAR ( 2000 )
DECLARE @ROWCOUNT INT
SELECT @SQLROC = '' , @DESCRIPTION = '' , @PARAMETER = '' , @REMARK = '' , @WHERE_SQL = '' , @SQL = '' ,
@HOST_NAME = HOST_NAME (), @GET_DATE = GETDATE ()
SET @SQLROC = @SQLROC + ' IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( '' SP_ ' + @TABLENAME + ' _Load '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
SET @SQLROC = @SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _Load ' + CHAR ( 10 )
SET @SQLROC = @SQLROC + ' GO '
SET NOCOUNT ON
CREATE TABLE #(TABLE_QUALIFIER VARCHAR ( 100 ),
TABLE_OWNER VARCHAR ( 100 ),
TABLE_NAME VARCHAR ( 100 ),
COLUMN_NAME VARCHAR ( 100 ),
KEY_SEQ VARCHAR ( 50 ),
PK_NAME VARCHAR ( 100 ))
INSERT INTO # EXEC SP_PKEYS @TABLENAME
SELECT @REMARK = @REMARK + ' ,@ ' + COLUMN_NAME,
@WHERE_SQL = @WHERE_SQL + ' AND ' + COLUMN_NAME + ' =@ ' + COLUMN_NAME + ''
FROM #
SELECT @PARAMETER = @PARAMETER + SPACE ( 4 ) + ' @ ' + LTRIM (NAME) + SPACE ( 20 - LEN (NAME)) +
CASE WHEN xtype = 34 THEN ' image '
WHEN xtype = 35 THEN ' text '
WHEN xtype = 36 THEN ' uniqueidentifier '
WHEN xtype = 48 THEN ' tinyint '
WHEN xtype = 52 THEN ' smallint '
WHEN xtype = 56 THEN ' int '
WHEN xtype = 58 THEN ' smalldatetime '
WHEN xtype = 59 THEN ' real '
WHEN xtype = 60 THEN ' money '
WHEN xtype = 61 THEN ' datetime '
WHEN xtype = 62 THEN ' float '
WHEN xtype = 98 THEN ' sql_variant '
WHEN xtype = 99 THEN ' ntext '
WHEN xtype = 104 THEN ' bit '
WHEN xtype = 106 THEN ' decimal '
WHEN xtype = 108 THEN ' numeric '
WHEN xtype = 122 THEN ' smallmoney '
WHEN xtype = 127 THEN ' bigint '
WHEN xtype = 165 THEN ' varbinary '
WHEN xtype = 167 THEN ' varchar ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 173 THEN ' binary '
WHEN xtype = 175 THEN ' char ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 189 THEN ' timestamp '
WHEN xtype = 231 THEN ' nvarchar ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 239 THEN ' nchar ' + ' ( ' + LTRIM (length) + ' ) '
WHEN xtype = 241 THEN ' xml '
WHEN xtype = 231 THEN ' sysname ' END + ' , ' + CHAR ( 10 )
FROM SYSCOLUMNS A
WHERE ID = OBJECT_ID ( '' + @TABLENAME + '' )
AND NAME IN ( SELECT COLUMN_NAME FROM #)
DROP TABLE #
SET NOCOUNT OFF
SET @DESCRIPTION = @DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 过程名称:SP_ ' + @TABLENAME + ' _Load ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 功能说明:查询表 ' + @TABLENAME + ' 记录集合的存储过程 ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 入口参数: ' + STUFF ( @REMARK , 1 , 1 , '' ) + '' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 过程返回:返回所有记录集 ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 维护记录:Y/A ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 使用案例:SP_ ' + @TABLENAME + ' _Load ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 工作站名: ' + @HOST_NAME + '' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 联系方式:zlp321001@hotmail.com ' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' | 创建日期: ' + CONVERT ( VARCHAR ( 20 ), @GET_DATE , 120 ) + '' + CHAR ( 10 )
SET @DESCRIPTION = @DESCRIPTION + ' +--------------------------------------+*/ ' + CHAR ( 10 )
SELECT @SQLROC = @SQLROC + CHAR ( 10 ) + @DESCRIPTION + ' CREATE PROC SP_ ' + @TABLENAME + ' _Load '
SET @SQLROC = @SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + LEFT ( @PARAMETER , LEN ( @PARAMETER ) - 2 ) + CHAR ( 10 )
SET @SQLROC = @SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
SET @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON '
SET @SQLROC = @SQLROC + CHAR ( 32 ) + @SQL
SET @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' SELECT * FROM ' + @TABLENAME + ' WHERE ' + STUFF ( @WHERE_SQL , 1 , 4 , '' ) + CHAR ( 10 )
SET @SQLROC = @SQLROC + SPACE ( 4 ) + ' SET NOCOUNT OFF '
SET @SQLROC = @SQLROC + CHAR ( 10 ) + ' END '
PRINT @SQLROC + CHAR ( 10 ) + ' GO '
END
GO
CREATE PROC #SP_Generation_Load
@TABLENAMES VARCHAR ( 8000 )
AS
BEGIN
DECLARE @I INT
DECLARE @TABLENAME VARCHAR ( 100 )
SET @I = CHARINDEX ( ' , ' , @TABLENAMES )
WHILE @I > 0
BEGIN
SET @TABLENAME = LEFT ( @TABLENAMES , @I - 1 )
EXEC #AutoGeneration_Load_P @TABLENAME
SET @TABLENAMES = RIGHT ( @TABLENAMES , LEN ( @TABLENAMES ) - @I )
SET @I = CHARINDEX ( ' , ' , @TABLENAMES )
END
IF LEN ( @TABLENAMES ) > 0
BEGIN
EXEC #AutoGeneration_Load_P @TABLENAMES
END
END
GO
-- 测试
#SP_Generation_Load ' t '
drop proc #AutoGeneration_Load_P
drop proc #SP_Generation_Load
-- 测试结果:
/**/ /*
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Load') AND XTYPE IN (N'P'))
DROP PROC SP_t_Load
GO
/*+--------------------------------------+
| 过程名称:SP_t_Load
| 功能说明:查询表t记录集合的存储过程
| 入口参数:@type
| 过程返回:返回所有记录集
| 维护记录:Y/A
| 使用案例:SP_t_Load
| 工作站名:RICHWAY-ZJ
| 联系方式:zlp321001@hotmail.com
| 创建日期:2006-08-31 12:26:37
+--------------------------------------+*/
CREATE PROC SP_t_Load
@type varchar ( 10 )
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM t WHERE type = @type
SET NOCOUNT OFF
END
GO
*/