通用存储过程之三:根据主键的值,查询记录的存储过程
来源:互联网 发布:电脑ip更换软件 编辑:程序博客网 时间:2024/05/16 07:34
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
*/
- 通用存储过程之三:根据主键的值,查询记录的存储过程
- 通用存储过程之三:根据主键的值,查询记录的存储过程
- 通用存储过程之二: 根据主键的值,判断记录是否存在的存储过程
- 通用存储过程之二: 根据主键的值,判断记录是否存在的存储过程
- 查询重复记录的通用存储过程
- 通用存储过程之五: 根据查询条件,获取表记录数的存储过程。
- 通用存储过程之五: 根据查询条件,获取表记录数的存储过程。
- 一个通用查询的存储过程
- 模糊查询的通用存储过程
- 通用的存储过程
- SQL之根据表名动态查询的存储过程
- SQL之通用查询分页存储过程
- SQL之通用查询分页存储过程
- 通用存储过程的编写
- 通用的分页存储过程
- 通用存储过程的编写
- 通用的分页存储过程
- 通用存储过程的编写
- 安装apache_2.0.58出错解决
- Windows Mobile bus driver的角色不可小看
- Jsp结合XML+XSLT将输出转换为Html格式
- 用图片作为提交按钮的方式
- JavaScript词法结构注意
- 通用存储过程之三:根据主键的值,查询记录的存储过程
- 一担挑游戏 (人工智能版)
- OWC学习笔记-Spreadsheet插入行/列
- 设计模式:实战MVC模式
- Email javascript 正则表达式
- 通用存储过程之二: 根据主键的值,判断记录是否存在的存储过程
- 阿里巴巴和环球资源两大外贸网站,我们该选谁?
- 通用存储过程之一:插入、更新、删除存储过程。
- 正则表达式(一)