帮我看看？

显示全部楼层 · 2005-10-30 17:05:33

我的存储过程错在那里？
我想利用存储过程创建一个临时表，请便我看看错在那里，他的表名和主键是动态的。报错在：
/*
create table @ResultTableName
(
Resultcode NUMERIC(20) IDENTITY(1,1) not null,
SourceObjName varchar(20) not null, nfo VARCHAR(256) null /*摘要描述*/
CONSTRAINT @PrimeKey PRIMARY KEY CLUSTERED (Resultcode)
)
*/
我的存储过程是：
CREATE PROCEDURE sp_CreateTable
AS
begin
declare @ncnt int
declare @ResultTableName varchar(14)
declare @PrimeKey varchar(20)
select @ncnt=count(name) FROM sysobjects
WHERE name like 'Result%' and convert(char(8),substring(name,7,6),112)=substring(convert(char(8),getdate(),112),1,6)
--创建临时表
if @ncnt=0
begin
select @ResultTableName = 'Result'+substring(convert(char(8),getdate(),112),1,6)
select @PrimeKey ='pk_'+@ResultTableName
/*
create table @ResultTableName
(
Resultcode NUMERIC(20) IDENTITY(1,1) not null,
SourceObjName varchar(20) not null, nfo VARCHAR(256) null /*摘要描述*/
CONSTRAINT @PrimeKey PRIMARY KEY CLUSTERED (Resultcode)
)
*/
end
end

千问 · 2005-10-30 17:05:33

下面是邮件内容。

alter PROCEDURE sp_CreateTable
AS
begin
declare @ncnt int
declare @ResultTableName varchar(14)
declare @PrimeKey varchar(20)
select @ncnt=count(name) FROM sysobjects
WHERE name like 'Result%' and convert(char(8),substring(name,7,6),112)=substring(convert(char(8),getdate(),112),1,6)
if @ncnt=0
begin
select @ResultTableName = 'Result'+substring(convert(char(8),getdate(),112),1,6)
select @PrimeKey ='pk_'+@ResultTableName
declare @op nvarchar(3000)
select @op = ''
select @op = @op + 'create table ' + @ResultTableName + '(' + char(13) + char(10)
select @op = @op + 'Resultcode NUMERIC(20) IDENTITY(1,1) not null, ' + char(13) + char(10)
select @op = @op + 'SourceObjName varchar(20) not null, nfo VARCHAR(256) null ' + char(13) + char(10)
select @op = @op + 'CONSTRAINT ' + @PrimeKey + ' PRIMARY KEY CLUSTERED (Resultcode) ' + char(13) + char(10)
select @op = @op + ')' + char(13) + char(10)
--print(@op)
exec(@op)
end
end
go

千问 · 2005-10-30 17:05:33

谢谢你！我收到了！