create proc dt_insertmovinfo @MovieID int , @CustomerID int , @AddDate datetime , @Operator varchar (50) , @KeyWord varchar (50) , @Hits int , @Recommendation tinyint , @Hidden tinyint , @DefinedUrl tinyint , @ServerID int , @ftpID int , @result int OUT , --1为成功,2为失败 @errorInfo nvarchar OUT --失败信息 AS DECLARE @maxsum int DECLARE @TableID int DECLARE @TName VARCHAR(100) DECLARE @TableN VARCHAR(100) DECLARE @rowcount int -- @rowcount 表现有行数,@TableID表中现ID号 @TName表名 ,@TableN表前缀名,@maxsum最大行数 SET @rowcount =0 SET @TableID=0 SET @TName=NULL SET @TableN='Movie_CustomerMovies' SET @maxsum=200000 SET @result = 0 SET @errorInfo =null --判断必输参数 IF (@MovieID is null or @CustomerID is null ) begin SET @result = 1 SET @errorInfo = '字段MovieID或Customer为null' goto endsign end SELECT @rowcount=MAXSUM,@TableID=NextID FROM maxvalue WHERE tabName=@TableN PRINT 'maxsum=' + CAST(@maxsum AS VARCHAR(50)) PRINT 'NextID='+ CAST(@TableID+1 AS VARCHAR(50)) begin transaction IF(@rowcount>=@maxsum) BEGIN SET @TableID=@TableID+1 PRINT @TableID if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TName+@TableID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[@TName+@TableID]
正确结果,一个插入的,一个检索的!!if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_insertmovinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[dt_insertmovinfo] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_selcoustomermov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[dt_selcoustomermov] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOCREATE PROCEDURE dt_insertmovinfo @MovieID int , @CustomerID int , @AddDate datetime , @Operator varchar (50) , @KeyWord varchar (50) , @Hits int , @Recommendation tinyint , @Hidden tinyint , @DefinedUrl tinyint , @ServerID int , @ftpID int AS DECLARE @maxsum int /*最大行数*/ DECLARE @TableID int /*表中现ID号*/ DECLARE @TName VARCHAR(100) /*表名*/ DECLARE @TableN VARCHAR(100) /*表前缀名*/ DECLARE @rowcount int /*表现有行数*/ DECLARE @strINSERTSQL VARCHAR(4000) /*SQL字符串*/
SET @rowcount =0 SET @TableID=0 SET @TName=NULL SET @TableN='Movie_CustomerMovies' SET @maxsum=100000 --判断必输参数 IF (@MovieID is null or @CustomerID is null ) begin RETURN (1) return end SELECT @rowcount=MAXSUM,@TableID=TableID FROM maxvalue WHERE tabName=@TableN BEGIN TRANSACTION IF(@rowcount>=@maxsum) BEGIN SET @TName=@TableN+CONVERT(VARCHAR(5),@TableID + 1) exec('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+@TName+']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+@TName)
if @@error != 0 BEGIN ROLLBACK TRAN RETURN(1) END COMMIT TRAN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOCREATE PROCEDURE dt_selcoustomermov @CoustomerID varchar(30) /*用户ID*/ AS DECLARE @TableSUM int /*现表数*/ DECLARE @strSELECTSQL VARCHAR(200) DECLARE @strSQL VARCHAR(2000) DECLARE @TableN VARCHAR(100) DECLARE @i int
SET @TableSUM = 1 SET @strSELECTSQL = ' SELECT * FROM Movie_CustomerMovies' SET @TableN ='Movie_CustomerMovies' IF (@CoustomerID is null) BEGIN RETURN END SELECT @TableSUM=TableID FROM maxvalue WHERE tabName=@TableN SET @i=1 SET @strSQL = @strSELECTSQL + CONVERT (VARCHAR(8),@i) + ' WHERE CustomerID = ''' + @CoustomerID + ''' ' SET @i=@i+1 WHILE @i<=@TableSUM BEGIN SET @strSQL = @strSQL + ' UNION ALL ' SET @strSQL = @strSQL + @strSELECTSQL + CONVERT (VARCHAR(8),@i) + ' WHERE CustomerID = ''' + @CoustomerID + ''' ' SET @i=@i+1 END PRINT @strSQL EXEC (@strSQL) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
movieslist中,谢谢
谢谢!
每次做新表的时候更新maxvalue中的值
说错了,sorry 改为:每次新建表就在maxvalue里面更新movieslist的maxvalue数据,就是加1!
@CustomerID int ,
@AddDate datetime ,
@Operator varchar (50) ,
@KeyWord varchar (50) ,
@Hits int ,
@Recommendation tinyint ,
@Hidden tinyint ,
@DefinedUrl tinyint ,
@ServerID int ,
@ftpID int ,
@result int OUT , --1为成功,2为失败
@errorInfo nvarchar OUT --失败信息
AS DECLARE @maxsum int
DECLARE @TableID int
DECLARE @TName VARCHAR(100)
DECLARE @TableN VARCHAR(100)
DECLARE @rowcount int
-- @rowcount 表现有行数,@TableID表中现ID号 @TName表名 ,@TableN表前缀名,@maxsum最大行数
SET @rowcount =0
SET @TableID=0
SET @TName=NULL
SET @TableN='Movie_CustomerMovies'
SET @maxsum=200000
SET @result = 0
SET @errorInfo =null --判断必输参数
IF (@MovieID is null or @CustomerID is null )
begin
SET @result = 1
SET @errorInfo = '字段MovieID或Customer为null'
goto endsign
end
SELECT @rowcount=MAXSUM,@TableID=NextID
FROM maxvalue
WHERE tabName=@TableN PRINT 'maxsum=' + CAST(@maxsum AS VARCHAR(50)) PRINT 'NextID='+ CAST(@TableID+1 AS VARCHAR(50)) begin transaction IF(@rowcount>=@maxsum)
BEGIN
SET @TableID=@TableID+1 PRINT @TableID if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TName+@TableID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@TName+@TableID]
CREATE TABLE [dbo].[@TName+@TableID] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[MovieID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[AddDate] [datetime] NULL ,
[Operator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[KeyWord] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Hits] [int] NULL ,
[Recommendation] [tinyint] NULL ,
[Hidden] [tinyint] NULL ,
[DefinedUrl] [tinyint] NULL ,
[ServerID] [int] NULL ,
[ftpID] [int] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[@TName+@TableID] WITH NOCHECK ADD
CONSTRAINT [DF_@TName+@TableID_1_AddDate] DEFAULT (getdate()) FOR [AddDate],
CONSTRAINT [DF_@TName+@TableID_1_Hits] DEFAULT (0) FOR [Hits],
CONSTRAINT [DF_@TName+@TableID_1_Recommendation] DEFAULT (0) FOR [Recommendation],
CONSTRAINT [DF_@TName+@TableID_1_Hidden] DEFAULT (0) FOR [Hidden],
CONSTRAINT [DF_@TName+@TableID_1_DefinedUrl] DEFAULT (0) FOR [DefinedUrl],
CONSTRAINT [DF_@TName+@TableID_1_ServerID] DEFAULT (0) FOR [ServerID],
CONSTRAINT [DF_@TName+@TableID_1_ftpID] DEFAULT (0) FOR [ftpID],
CONSTRAINT [PK_@TName+@TableID_1] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY] UPDATE maxvalue Set TableID=@TableID WHERE tabName=@TName
END INSERT INTO @TableID (CustomerID ,
AddDate ,Operator ,KeyWord ,Hits,Recommendation ,Hidden ,DefinedUrl ,ServerID ,ftpID )
VALULES (@CustomerID,@AddDate,@Operator,@KeyWord,@Hits,@Recommendation,@Hidden,@DefinedUrl
@ServerID,@ftpID)
if @@error != 0
BEGIN
goto RollbackAndReturn
SET @result =0
SET @errorInfo= ''
end
commit
endsign:GO服务器: 消息 170,级别 15,状态 1,过程 dt_insertmovinfo,行 96
第 96 行: 'PRIMARY' 附近有语法错误。
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@TableID'。
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: ',' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,行 13
必须声明变量 '@result'。
服务器: 消息 137,级别 15,状态 1,行 14
必须声明变量 '@errorInfo'。帮看下哪有问题,我没看出来 谢谢!!
drop procedure [dbo].[dt_insertmovinfo]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_selcoustomermov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_selcoustomermov]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE PROCEDURE dt_insertmovinfo
@MovieID int ,
@CustomerID int ,
@AddDate datetime ,
@Operator varchar (50) ,
@KeyWord varchar (50) ,
@Hits int ,
@Recommendation tinyint ,
@Hidden tinyint ,
@DefinedUrl tinyint ,
@ServerID int ,
@ftpID int
AS DECLARE @maxsum int /*最大行数*/
DECLARE @TableID int /*表中现ID号*/
DECLARE @TName VARCHAR(100) /*表名*/
DECLARE @TableN VARCHAR(100) /*表前缀名*/
DECLARE @rowcount int /*表现有行数*/
DECLARE @strINSERTSQL VARCHAR(4000) /*SQL字符串*/
SET @rowcount =0
SET @TableID=0
SET @TName=NULL
SET @TableN='Movie_CustomerMovies'
SET @maxsum=100000
--判断必输参数
IF (@MovieID is null or @CustomerID is null )
begin
RETURN (1)
return
end SELECT @rowcount=MAXSUM,@TableID=TableID
FROM maxvalue
WHERE tabName=@TableN BEGIN TRANSACTION IF(@rowcount>=@maxsum)
BEGIN
SET @TName=@TableN+CONVERT(VARCHAR(5),@TableID + 1)
exec('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+@TName+']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table '+@TName)
EXEC('CREATE TABLE [dbo].[' + @TName + '] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[MovieID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[AddDate] [datetime] NULL ,
[Operator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[KeyWord] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Hits] [int] NULL ,
[Recommendation] [tinyint] NULL ,
[Hidden] [tinyint] NULL ,
[DefinedUrl] [tinyint] NULL ,
[ServerID] [int] NULL ,
[ftpID] [int] NULL
) ON [PRIMARY] ')
SET @strINSERTSQL =' ALTER TABLE [dbo].[' + @TName + '] WITH NOCHECK ADD
CONSTRAINT [PK_' + @TName + '_1] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY] ALTER TABLE [dbo].[' + @TName + '] WITH NOCHECK ADD
CONSTRAINT [DF_' + @TName + '_1_AddDate] DEFAULT (getdate()) FOR [AddDate],
CONSTRAINT [DF_' + @TName + '_1_Hits] DEFAULT (0) FOR [Hits],
CONSTRAINT [DF_' + @TName + '_1_Recommendation] DEFAULT (0) FOR [Recommendation],
CONSTRAINT [DF_' + @TName + '_1_Hidden] DEFAULT (0) FOR [Hidden],
CONSTRAINT [DF_' + @TName + '_1_DefinedUrl] DEFAULT (0) FOR [DefinedUrl],
CONSTRAINT [DF_' + @TName + '_1_ServerID] DEFAULT (0) FOR [ServerID],
CONSTRAINT [DF_' + @TName + '_1_ftpID] DEFAULT (0) FOR [ftpID]
CREATE UNIQUE INDEX [IX_' + @TName + '_1] ON [dbo].[' + @TName + ']([MovieID], [CustomerID]) WITH IGNORE_DUP_KEY ON [PRIMARY] '
exec(@strINSERTSQL) EXEC('UPDATE maxvalue Set TableID=TableID + 1,MAXSUM=0 WHERE tabName=''' + @TableN +'''')
END
ELSE
BEGIN
SET @TName=@TableN+CONVERT(VARCHAR(5),@TableID )
EXEC('UPDATE maxvalue Set MAXSUM=MAXSUM+1 WHERE tabName=''' + @TableN +'''')
END SET @strINSERTSQL = 'INSERT INTO '
+ @TName
+ '(MovieID,CustomerID ,AddDate ,Operator ,KeyWord ,Hits,Recommendation ,Hidden ,DefinedUrl ,ServerID ,ftpID ) VALUES ('''
+ CONVERT(VARCHAR(50), @MovieID )+ ''','''
+ CONVERT(VARCHAR(50), @CustomerID )+ ''','''
+ CONVERT(VARCHAR(50), @AddDate ) + ''','''
+ CONVERT(VARCHAR(50), @Operator ) + ''','''
+ CONVERT(VARCHAR(50), @KeyWord ) + ''','''
+ CONVERT(VARCHAR(50), @Hits ) + ''','''
+ CONVERT(VARCHAR(50), @Recommendation ) + ''','''
+ CONVERT(VARCHAR(50), @Hidden ) + ''','''
+ CONVERT(VARCHAR(50), @DefinedUrl ) + ''','''
+ CONVERT(VARCHAR(50) ,@ServerID ) + ''','''
+ CONVERT(VARCHAR(50), @ftpID ) + ''')'
EXEC(@strINSERTSQL)
if @@error != 0
BEGIN
ROLLBACK TRAN
RETURN(1)
END
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE PROCEDURE dt_selcoustomermov
@CoustomerID varchar(30) /*用户ID*/
AS
DECLARE @TableSUM int /*现表数*/
DECLARE @strSELECTSQL VARCHAR(200)
DECLARE @strSQL VARCHAR(2000)
DECLARE @TableN VARCHAR(100)
DECLARE @i int
SET @TableSUM = 1
SET @strSELECTSQL = ' SELECT * FROM Movie_CustomerMovies'
SET @TableN ='Movie_CustomerMovies' IF (@CoustomerID is null)
BEGIN
RETURN
END SELECT @TableSUM=TableID
FROM maxvalue
WHERE tabName=@TableN
SET @i=1 SET @strSQL = @strSELECTSQL + CONVERT (VARCHAR(8),@i) + ' WHERE CustomerID = ''' + @CoustomerID + ''' '
SET @i=@i+1
WHILE @i<=@TableSUM
BEGIN
SET @strSQL = @strSQL + ' UNION ALL '
SET @strSQL = @strSQL + @strSELECTSQL + CONVERT (VARCHAR(8),@i) + ' WHERE CustomerID = ''' + @CoustomerID + ''' '
SET @i=@i+1
END
PRINT @strSQL
EXEC (@strSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO