通用存储过程之三：根据主键的值,查询记录的存储过程

<script type="text/javascript">function StorePage(){d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();}</script>

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 + ' | 联系方式：[e-mail] ' + 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
| 联系方式：[e-mail]
| 创建日期：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  

*/