请各位看一下如下错误的sql语句如何更改正确实现需要的功能

[pzp]

主要是 insert into @cCurrDbTbl(FileId,[Date], [Time]) values(@iFileId,@iDate, @iTime)
这句语句有问题，请问该如何实现它。

Create Procedure [dbo].[sp_TestTbl]
@id int OUTPUT,
@cCurrDbName varchar(100),
@iFileId int,
@iDate int,
@iTime int
AS
BEGIN
begin tran
declare @cCurrDbTbl varchar(255)
select @cCurrDbTbl=@cCurrDbName+'.dbo.T_TestTbl'
insert into @cCurrDbTbl(FileId,[Date], [Time]) values(@iFileId,@iDate, @iTime)

if(@@error <> 0)
begin
rollback tran
return 0
end

select @id = @@identity
commit tran
return 1
END

[QQMagicer]

Create Procedure [dbo].[sp_TestTbl]
@id int OUTPUT,
@cCurrDbName varchar(100),
@iFileId int,
@iDate int,
@iTime int
AS
BEGIN
begin tran
declare @cCurrDbTbl varchar(255)
select @cCurrDbTbl=@cCurrDbName+'.dbo.T_TestTbl'

--改称如下结果就可以了，作为字符串执行

exec ('insert into'+ @cCurrDbTbl+'(FileId,[Date], [Time]) values(@iFileId,@iDate,


@iTime)')

if(@@error <> 0)
begin
rollback tran
return 0
end

select @id = @@identity
commit tran
return 1
END

[mschen]

--修改了一下.

Create Procedure [dbo].[sp_TestTbl]
@id int OUTPUT,
@cCurrDbName varchar(100),
@iFileId int,
@iDate int,
@iTime int
AS
declare @str_sql varchar(8000)
begin tran
declare @cCurrDbTbl varchar(255)
select @cCurrDbTbl=@cCurrDbName+'.dbo.T_TestTbl'
set @str_sql='
insert into @cCurrDbTbl(FileId,[Date], [Time]) values(@iFileId,@iDate, @iTime)'
sp_executesql @str_sql,
N'@cCurrDbTbl varchar(255),@iFileId int,@iDate int,@iTime int',
@cCurrDbTbl,@iFileId,@iDate,@iTime
if(@@error <> 0)
begin
rollback tran
return 0
end

select @id = @@identity
commit tran
return 1
Go

[pzp]

基本上可以了。exec ('insert into'+ @cCurrDbTbl+'(FileId,[Date], [Time]) values('+@iFileId+','+@iDate+','+@iTime+')')这句需要把int用转换函数转换成字符串。

[pzp]

回复人： mschen(发光的星星) :在查询分析器里执行编译不过去。
回复人： QQMagicer() ( )：你的方法我也用了，但执行这个存储过程时给其传递参数一直都执行不成功,报些莫名的错误，估计还是存储过程写的有问题。例如： exec sp_imptesttbl 'db1',1,20050505,100222

[QQMagicer]

--存储过程改一下，按照下面的再重新执行一下

Create Procedure [dbo].[sp_TestTbl]
@cCurrDbName varchar(100),
@iFileId int,
@iDate int,
@iTime int,
@id int OUTPUT

AS
BEGIN
begin tran
declare @cCurrDbTbl varchar(255)
select @cCurrDbTbl=@cCurrDbName+'.dbo.T_TestTbl'

--改称如下结果就可以了，作为字符串执行

exec ('insert into'+ @cCurrDbTbl+'(FileId,[Date], [Time]) values('+@iFileId+','+@iDate+','+@iTime+')')

if(@@error <> 0)
begin
rollback tran
return 0
end

select @id = @@identity
commit tran
return 1
END


--执行语句的时候这么写，因为你有output参数，所以要有接收的变量才行

declare @id int
exec sp_TestTbl 'db1',1,20050505,100222,@id output
select @id

这个你在试试吧

[QQMagicer]

--重新改一下过程
Create Procedure [dbo].[sp_TestTbl]
@cCurrDbName varchar(100),
@iFileId int,
@iDate int,
@iTime int,
@id int OUTPUT --output的参数最好写在最后面
AS
BEGIN
begin tran
declare @cCurrDbTbl varchar(255)
select @cCurrDbTbl=@cCurrDbName+'.dbo.T_TestTbl'
exec ('insert into'+ @cCurrDbTbl+'(FileId,[Date], [Time]) values('+@iFileId+','+@iDate+','+@iTime+')')
if(@@error <> 0)
begin
rollback tran
return 0
end
select @id = @@identity
commit tran
return 1
END
--执行如下语句，有output的参数，执行时需要有接收的变量
declare @id int
exec sp_TestTbl 'db1',1,20050505,100222,@id output
select @id