通用存储过程之一：插入、更新、删除存储过程。-优快云博客

--临时存储过程如下：

CREATE PROC #AutoGeneration_IUD_P

@TABLENAME VARCHAR(50),

@FLAT TINYINT

BEGIN

DECLARE @HOST_NAME VARCHAR(200)

DECLARE @GET_DATE DATETIME

SELECT @HOST_NAME=HOST_NAME(),@GET_DATE=GETDATE()

IF @FLAT=1 --插入

BEGIN

DECLARE @INSERT_SQLROC NVARCHAR(4000)

DECLARE @INSERT_SQL VARCHAR(8000)

DECLARE @INSERT_PARAMETER VARCHAR(8000)

DECLARE @INSERT_DESCRIPTION VARCHAR(8000)

DECLARE @INSERT_REMARK VARCHAR(2000)

DECLARE @INSERT_COLUMN VARCHAR(8000)

SELECT @INSERT_SQLROC='',@INSERT_SQL='',@INSERT_PARAMETER='',@INSERT_DESCRIPTION='',

@INSERT_REMARK='',@INSERT_COLUMN=''

SET @INSERT_SQLROC=@INSERT_SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_AddNew'') AND XTYPE IN (N''P''))'+CHAR(10)

SET @INSERT_SQLROC=@INSERT_SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_AddNew'+CHAR(10)

SET @INSERT_SQLROC=@INSERT_SQLROC+'GO '

SELECT @INSERT_PARAMETER=@INSERT_PARAMETER+SPACE(4)+'@'+LTRIM(NAME)+SPACE(15-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),

@INSERT_REMARK=@INSERT_REMARK+',@'+NAME,

@INSERT_COLUMN=@INSERT_COLUMN+','+NAME

FROM

(

SELECT NAME,CDEFAULT,A.ID,XTYPE,LENGTH,COLORDER

FROM SYSCOLUMNS A

WHERE A.ID=OBJECT_ID(''+@TABLENAME+'')

AND COLUMNPROPERTY(A.ID,A.NAME,'IsIdentity')<>1

)TB

ORDER BY COLORDER

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'/*+--------------------------------------+'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 过程名称：SP_'+@TABLENAME+'_AddNew'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 功能说明：插入表'+@TABLENAME+'的存储过程'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 入口参数：'+STUFF(@INSERT_REMARK,1,1,'')+''+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 过程返回：无返回记录'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 维护记录：Y/A'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 使用案例：SP_'+@TABLENAME+'_AddNew'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 工作站名：'+@HOST_NAME+''+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 联系方式：ZLP321001@HOTMAIL.COM'+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'| 创建日期：'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10)

SET @INSERT_DESCRIPTION=@INSERT_DESCRIPTION+'+--------------------------------------+*/'+CHAR(10)

SELECT @INSERT_SQLROC=@INSERT_SQLROC+CHAR(10)+@INSERT_DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_AddNew'

SET @INSERT_SQLROC=@INSERT_SQLROC+CHAR(13)+CHAR(10)+LEFT(@INSERT_PARAMETER,LEN(@INSERT_PARAMETER)-2)+CHAR(10)

SET @INSERT_SQLROC=@INSERT_SQLROC+'AS'+CHAR(10)+'BEGIN'

SET @INSERT_SQLROC=@INSERT_SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON'

SET @INSERT_SQL=SPACE(8)+'INSERT INTO '+@TABLENAME+'('+STUFF(@INSERT_COLUMN,1,1,'')+')'+CHAR(13)+CHAR(10)+SPACE(8)+' SELECT '+STUFF(@INSERT_REMARK,1,1,'')

SET @INSERT_SQLROC=@INSERT_SQLROC+CHAR(10)+CHAR(32)+@INSERT_SQL

SET @INSERT_SQLROC=@INSERT_SQLROC+CHAR(10)+SPACE(8)+' SELECT _ROWCOUNT=@@ROWCOUNT'

SET @INSERT_SQLROC=@INSERT_SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT OFF'

SET @INSERT_SQLROC=@INSERT_SQLROC+CHAR(10)+'END'

PRINT @INSERT_SQLROC+CHAR(10)+'GO '

--EXEC(@INSERT_SQLROC)

--EXEC SP_EXECUTESQL @INSERT_SQLROC

END

IF @FLAT=2 --修改

BEGIN

DECLARE @UPDATE_SQLROC VARCHAR(8000)

DECLARE @UPDATE_SQL VARCHAR(8000)

DECLARE @UPDATE_PARAMETER VARCHAR(1000)

DECLARE @UPDATE_DESCRIPTION VARCHAR(1000)

DECLARE @UPDATE_REMARK VARCHAR(1000)

DECLARE @UPDATE_KEY_COLUMN VARCHAR(1000)

DECLARE @UPDATE_COLUMN VARCHAR(5000)

SELECT @UPDATE_SQLROC='',

@UPDATE_SQL='',@UPDATE_PARAMETER='',@UPDATE_DESCRIPTION='',

@UPDATE_REMARK='',@UPDATE_KEY_COLUMN='',@UPDATE_COLUMN=''

SET @UPDATE_SQLROC=@UPDATE_SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Update'') AND XTYPE IN (N''P''))'+CHAR(10)

SET @UPDATE_SQLROC=@UPDATE_SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Update'+CHAR(10)

SET @UPDATE_SQLROC=@UPDATE_SQLROC+' GO '

SELECT @UPDATE_PARAMETER=@UPDATE_PARAMETER+SPACE(4)+'@'+LTRIM(NAME)+SPACE(15-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+'')

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 @UPDATE_REMARK=@UPDATE_REMARK+','+COLUMN_NAME+'=@'+COLUMN_NAME

FROM #

SELECT @UPDATE_KEY_COLUMN=@UPDATE_KEY_COLUMN+','+NAME+'=@'+NAME

FROM SYSCOLUMNS A

WHERE ID=OBJECT_ID(''+@TABLENAME+'')

AND NAME NOT IN (SELECT COLUMN_NAME FROM #)

DROP TABLE #

SET NOCOUNT OFF

IF DATALENGTH(@UPDATE_PARAMETER)>0

BEGIN

SET @UPDATE_PARAMETER=LEFT(@UPDATE_PARAMETER,LEN(@UPDATE_PARAMETER)-2)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'/*+--------------------------------------+'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 过程名称：SP_'+@TABLENAME+'_Update'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 功能说明：更新表'+@TABLENAME+'的存储过程'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 入口参数：'+STUFF(@UPDATE_REMARK,1,1,'')+''+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 过程返回：无返回记录'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 维护记录：Y/A'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 使用案例：SP_'+@TABLENAME+'_Update'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 工作站名：'+@HOST_NAME+''+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 联系方式：ZLP321001@HOTMAIL.COM'+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'| 创建日期：'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10)

SET @UPDATE_DESCRIPTION=@UPDATE_DESCRIPTION+'+--------------------------------------+*/'+CHAR(10)

SELECT @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+@UPDATE_DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_Update'

SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(13)+CHAR(10)+@UPDATE_PARAMETER+CHAR(10)

SET @UPDATE_SQLROC=@UPDATE_SQLROC+'AS'+CHAR(10)+'BEGIN'

SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON'+CHAR(10)

SET @UPDATE_SQL=SPACE(8)+'UPDATE '+@TABLENAME+CHAR(10)+SPACE(8)+'SET '

SET @UPDATE_SQLROC=@UPDATE_SQLROC+@UPDATE_SQL+STUFF(@UPDATE_KEY_COLUMN,1,1,'')

SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(8)+'FROM '+@TABLENAME+CHAR(10)+SPACE(8)+'WHERE '+STUFF(@UPDATE_REMARK,1,1,'')+''

SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(8)+'SELECT _ROWCOUNT=@@ROWCOUNT'

SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT OFF'

SET @UPDATE_SQLROC=@UPDATE_SQLROC+CHAR(10)+'END'

PRINT @UPDATE_SQLROC+CHAR(13)+CHAR(10)+'GO'

--EXECUTE(@UPDATE_SQLROC+' GO ')

END

IF @FLAT=3 --删除

BEGIN

DECLARE @DELETE_SQLROC VARCHAR(8000)

DECLARE @DELETE_SQL VARCHAR(8000)

DECLARE @DELETE_PARAMETER VARCHAR(1000)

DECLARE @DELETE_DESCRIPTION VARCHAR(1000)

DECLARE @DELETE_REMARK VARCHAR(1000)

DECLARE @DELETE_KEY_COLUMN VARCHAR(1000)

SELECT @DELETE_SQLROC='',@DELETE_SQL='',@DELETE_PARAMETER='',@DELETE_DESCRIPTION='',

@DELETE_REMARK='',@DELETE_KEY_COLUMN=''

SET @DELETE_SQLROC=@DELETE_SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Remove'') AND XTYPE IN (N''P''))'+CHAR(10)

SET @DELETE_SQLROC=@DELETE_SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Remove'+CHAR(10)

SET @DELETE_SQLROC=@DELETE_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 @DELETE_KEY_COLUMN=@DELETE_KEY_COLUMN+','+COLUMN_NAME+'=@'+COLUMN_NAME,

@DELETE_REMARK=@DELETE_REMARK+',@'+COLUMN_NAME

FROM #_#

SELECT @DELETE_PARAMETER=@DELETE_PARAMETER+SPACE(4)+'@'+LTRIM(NAME)+SPACE(15-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

IF DATALENGTH(@DELETE_PARAMETER)>0

BEGIN

SET @DELETE_PARAMETER=LEFT(@DELETE_PARAMETER,LEN(@DELETE_PARAMETER)-2)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'/*+--------------------------------------+'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 过程名称：SP_'+@TABLENAME+'_Remove'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 功能说明：删除表'+@TABLENAME+'的存储过程'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 入口参数：'+STUFF(@DELETE_REMARK,1,1,'')+''+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 过程返回：无返回记录'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 维护记录：Y/A'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 使用案例：SP_'+@TABLENAME+'_Remove'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 工作站名：'+@HOST_NAME+''+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 联系方式：ZLP321001@HOTMAIL.COM'+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'| 创建日期：'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10)

SET @DELETE_DESCRIPTION=@DELETE_DESCRIPTION+'+--------------------------------------+*/'+CHAR(10)

SELECT @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+@DELETE_DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_Remove'

SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(13)+CHAR(10)+@DELETE_PARAMETER+CHAR(10)

SET @DELETE_SQLROC=@DELETE_SQLROC+'AS'+CHAR(10)+'BEGIN'

SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON'

SET @DELETE_SQL='DELETE '+@TABLENAME+CHAR(10)+SPACE(8)+'WHERE '

SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(8)+@DELETE_SQL+STUFF(@DELETE_KEY_COLUMN,1,1,'')

SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(8)+'SELECT _ROWCOUNT=@@ROWCOUNT'

SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT OFF'

SET @DELETE_SQLROC=@DELETE_SQLROC+CHAR(10)+'END'

PRINT @DELETE_SQLROC+CHAR(13)+CHAR(10)+'GO'

--EXECUTE(@DELETE_SQLROC+' GO ')

END

CREATE PROC #SP_Generation_IUD

@TABLENAMES VARCHAR(8000)

BEGIN

DECLARE @I INT

DECLARE @TABLENAME VARCHAR(100)

SET @I=CHARINDEX(',',@TABLENAMES)

WHILE @I>0

BEGIN

SET @TABLENAME=LEFT(@TABLENAMES,@I-1)

EXEC #AutoGeneration_IUD_P @TABLENAME,1

EXEC #AutoGeneration_IUD_P @TABLENAME,2

EXEC #AutoGeneration_IUD_P @TABLENAME,3

SET @TABLENAMES=RIGHT(@TABLENAMES,LEN(@TABLENAMES)-@I)

SET @I=CHARINDEX(',',@TABLENAMES)

END

IF LEN(@TABLENAMES)>0

BEGIN

EXEC #AutoGeneration_IUD_P @TABLENAMES,1

EXEC #AutoGeneration_IUD_P @TABLENAMES,2

EXEC #AutoGeneration_IUD_P @TABLENAMES,3

END

--调用方法如下: exec #SP_Generation_IUD '表1,表2,表3'

--Example：

exec #SP_Generation_IUD 'ICR_PermittedUrl,ICR_ForbiddenUrl'

DROP PROC #SP_Generation_IUD

DROP PROC #AutoGeneration_IUD_P

--输出结果如下：

/**//*

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_ICR_PermittedUrl_AddNew') AND XTYPE IN (N'P'))

DROP PROC SP_ICR_PermittedUrl_AddNew

/*+--------------------------------------+

| 过程名称：SP_ICR_PermittedUrl_AddNew

| 功能说明：插入表ICR_PermittedUrl的存储过程

| 入口参数：@ID,@URL,@State,@CategoryID,@Name,@Description,@DateCreated

| 过程返回：无返回记录

| 维护记录：Y/A

| 使用案例：SP_ICR_PermittedUrl_AddNew

| 工作站名：TOSHIBA

| 联系方式：ZLP321001@HOTMAIL.COM

| 创建日期：2006-08-12 19:18:37

+--------------------------------------+*/

CREATE PROC SP_ICR_PermittedUrl_AddNew

@ID int,

@URL varchar(100),

@State tinyint,

@CategoryID int,

@Name varchar(50),

@Description varchar(200),

@DateCreated datetime

BEGIN

SET NOCOUNT ON

INSERT INTO ICR_PermittedUrl(ID,URL,State,CategoryID,Name,Description,DateCreated)

SELECT @ID,@URL,@State,@CategoryID,@Name,@Description,@DateCreated

SELECT _ROWCOUNT=@@ROWCOUNT

SET NOCOUNT OFF

END

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_ICR_PermittedUrl_Update') AND XTYPE IN (N'P'))

DROP PROC SP_ICR_PermittedUrl_Update

/**//*+--------------------------------------+

| 过程名称：SP_ICR_PermittedUrl_Update

| 功能说明：更新表ICR_PermittedUrl的存储过程

| 入口参数：ID=@ID

| 过程返回：无返回记录

| 维护记录：Y/A

| 使用案例：SP_ICR_PermittedUrl_Update

| 工作站名：TOSHIBA

| 联系方式：ZLP321001@HOTMAIL.COM

| 创建日期：2006-08-12 19:18:37

+--------------------------------------+*/

CREATE PROC SP_ICR_PermittedUrl_Update

@ID int,

@URL varchar(100),

@State tinyint,

@CategoryID int,

@Name varchar(50),

@Description varchar(200),

@DateCreated datetime

BEGIN

SET NOCOUNT ON

UPDATE ICR_PermittedUrl

SET URL=@URL,State=@State,CategoryID=@CategoryID,Name=@Name,Description=@Description,DateCreated=@DateCreated

FROM ICR_PermittedUrl

WHERE ID=@ID

SELECT _ROWCOUNT=@@ROWCOUNT

SET NOCOUNT OFF

END

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_ICR_PermittedUrl_Remove') AND XTYPE IN (N'P'))

DROP PROC SP_ICR_PermittedUrl_Remove

/**//*+--------------------------------------+

| 过程名称：SP_ICR_PermittedUrl_Remove

| 功能说明：删除表ICR_PermittedUrl的存储过程

| 入口参数：@ID

| 过程返回：无返回记录

| 维护记录：Y/A

| 使用案例：SP_ICR_PermittedUrl_Remove

| 工作站名：TOSHIBA

| 联系方式：ZLP321001@HOTMAIL.COM

| 创建日期：2006-08-12 19:18:39

+--------------------------------------+*/

CREATE PROC SP_ICR_PermittedUrl_Remove

@ID int

BEGIN

SET NOCOUNT ON

DELETE ICR_PermittedUrl

WHERE ID=@ID

SELECT _ROWCOUNT=@@ROWCOUNT

SET NOCOUNT OFF

END

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_ICR_ForbiddenUrl_AddNew') AND XTYPE IN (N'P'))

DROP PROC SP_ICR_ForbiddenUrl_AddNew

/**//*+--------------------------------------+

| 过程名称：SP_ICR_ForbiddenUrl_AddNew

| 功能说明：插入表ICR_ForbiddenUrl的存储过程

| 入口参数：@ID,@URL,@State,@CategoryID,@Name,@Description,@DateCreated

| 过程返回：无返回记录

| 维护记录：Y/A

| 使用案例：SP_ICR_ForbiddenUrl_AddNew

| 工作站名：TOSHIBA

| 联系方式：ZLP321001@HOTMAIL.COM

| 创建日期：2006-08-12 19:18:39

+--------------------------------------+*/

CREATE PROC SP_ICR_ForbiddenUrl_AddNew

@ID int,

@URL varchar(100),

@State tinyint,

@CategoryID int,

@Name varchar(50),

@Description varchar(200),

@DateCreated datetime

BEGIN

SET NOCOUNT ON

INSERT INTO ICR_ForbiddenUrl(ID,URL,State,CategoryID,Name,Description,DateCreated)

SELECT @ID,@URL,@State,@CategoryID,@Name,@Description,@DateCreated

SELECT _ROWCOUNT=@@ROWCOUNT

SET NOCOUNT OFF

END

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_ICR_ForbiddenUrl_Update') AND XTYPE IN (N'P'))

DROP PROC SP_ICR_ForbiddenUrl_Update

/**//*+--------------------------------------+

| 过程名称：SP_ICR_ForbiddenUrl_Update

| 功能说明：更新表ICR_ForbiddenUrl的存储过程

| 入口参数：ID=@ID

| 过程返回：无返回记录

| 维护记录：Y/A

| 使用案例：SP_ICR_ForbiddenUrl_Update

| 工作站名：TOSHIBA

| 联系方式：ZLP321001@HOTMAIL.COM

| 创建日期：2006-08-12 19:18:39

+--------------------------------------+*/

CREATE PROC SP_ICR_ForbiddenUrl_Update

@ID int,

@URL varchar(100),

@State tinyint,

@CategoryID int,

@Name varchar(50),

@Description varchar(200),

@DateCreated datetime

BEGIN

SET NOCOUNT ON

UPDATE ICR_ForbiddenUrl

SET URL=@URL,State=@State,CategoryID=@CategoryID,Name=@Name,Description=@Description,DateCreated=@DateCreated

FROM ICR_ForbiddenUrl

WHERE ID=@ID

SELECT _ROWCOUNT=@@ROWCOUNT

SET NOCOUNT OFF

END

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_ICR_ForbiddenUrl_Remove') AND XTYPE IN (N'P'))

DROP PROC SP_ICR_ForbiddenUrl_Remove

/**//*+--------------------------------------+

| 过程名称：SP_ICR_ForbiddenUrl_Remove

| 功能说明：删除表ICR_ForbiddenUrl的存储过程

| 入口参数：@ID

| 过程返回：无返回记录

| 维护记录：Y/A

| 使用案例：SP_ICR_ForbiddenUrl_Remove

| 工作站名：TOSHIBA

| 联系方式：ZLP321001@HOTMAIL.COM

| 创建日期：2006-08-12 19:18:39

+--------------------------------------+*/

CREATE PROC SP_ICR_ForbiddenUrl_Remove

@ID int

BEGIN

SET NOCOUNT ON

DELETE ICR_ForbiddenUrl

WHERE ID=@ID

SELECT _ROWCOUNT=@@ROWCOUNT

SET NOCOUNT OFF

END